0

I have a lot to learn of Power BI and I am currently stuck with a column I need to add in my Calendar table.

I have to calculate a workday column that should flag each day of the month with it's workday number (from 1 to the maximum number of working days for that month).

I was able to find a solution online with RANKX (I have a column, NonWorkday that flags with 1 for all days that are either weekends or holidays) but it's not complete:

Workday = 
VAR CurrentMonth = Calendar[MonthYear]
VAR MonthTable = FILTER( ALL( Calendar ), Calendar[NonWorkDay] = 0 && Calendar[MonthYear] = CurrentMonth )
RETURN
IF( Calendar[NonWorkDay] = 1, BLANK(), RANKX(MonthTable, CALCULATE(AVERAGE(Calendar[DayofMonth])), ,ASC))

What I'm missing is that, for weekends and holidays I don't want blank, I want it to have the same workday number as the previous workday and if the month starts with a non-working day, that will be 0. An example image below:

Desired Workday

It would be great if anyone can help me with a solution or point me in the right direction, either DAX or M query.

Thank you!

LE: Adding MVE code, as per @Marco_CH request:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdJBDoMwDATAv+RciXgTSPIW1P9/oxTbar34wGUQCWvveRbZZEMFyqvI9dTyfp0FGbYMe4b7D+v94otHRNw4IrYbZzxTcWUoNVWJp3ZVRN1VW9RDtUcdqnu8zfRIlZJN1Rl1qS6aly2hEuscQelEJwnamXPLmQKKTg68OR0daHeiswNlFB0eKKToREAbNG41Z05pA9zwbBYiwtqKZ7N6xG5txf8PdGtrgiN+vltb8SzQiji8rUg6IfGq6W3NtKVKsZa3FUmr5CAW7yuSVgmls57Iiv/h9ak589a6NxZZqyijM4X0snHK4Y2Nh0xvbMqc8prg+wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, NonWorkDay = _t, WantedWorkday = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"NonWorkDay", Int64.Type}, {"WantedWorkday", Int64.Type}})
    in
        #"Changed Type"

LE2: My final calculation based on the answered provided by @msta42a:

WD = 
var day ='Calendar'[Date]
VAR CurrentMonth = Calendar[MonthYear]
return
CALCULATE( COUNTROWS('Calendar'), FILTER('Calendar', Calendar[MonthYear] = CurrentMonth && 'Calendar'[Date] <= day && 'Calendar'[NonWorkDay] <> 1)) +0

Results: WD Result

WhatsThat
  • 3
  • 4
  • Hi and welcome WhatsThat, please provide a reproducible MVE (minimal, workable example). [In this thread you'll find two ways how you easily can share data.](https://stackoverflow.com/a/70773971/12242625) Thank you – Marco_CH Jan 20 '22 at 08:10
  • @Marco_CH thanks for the suggestion and instruction link! – WhatsThat Jan 20 '22 at 12:23
  • If anyone knows how to do this in Power Query, I'm sure it will be helpful to have that answer here as well :) – WhatsThat Jan 26 '22 at 09:04

1 Answers1

1

You can calculate this like "running count/total" here my example (in your code you should add a clause for month/year)

Workday = 
var day ='Calend'[Date]
return
CALCULATE( COUNTROWS('Calend'), FILTER('Calend',  'Calend'[Date] <= day && 'Calend'[Weekend]="wd" && Calend[Nonworking] <> 0)) +0

enter image description here

msta42a
  • 3,601
  • 1
  • 4
  • 14