Calculate past workday in current quarter

[HLVW]

Hi.

I need to create a measure that shows the number of past working days in current quarter. I already have a measure for the month, pls. see below, but I cant seem to get the quarter to work....

WorkingDays before today:= var _date = TODAY()
return
CALCULATE(
DISTINCTCOUNT('Date'[Date]),
FILTER(
ALL('Date'),
'Date'[Date]<DATE(YEAR(_date),MONTH(_date),DAY(_date))&&
'Date'[Date]>=DATE(YEAR(_date),MONTH(_date),1)&&
'Date'[IsWorkDayKey]=1
)
)

In my date table I already have Quarter name, f.ex. Q2.

Thanks a lot in advance.

[v-yifanw-msft]

Thank you @bhanu_gautam   for your prompt reply.

Hi @HLVW  ,
May I ask if your problem has been solved? If the problem has not yet been solved, please feel free to ask us a question. Would you be able to provide more data (without private information) so that we can better understand and solve the problem you are experiencing.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HLVW]

Hi @v-yifanw-msft 

I had to make some adjustments in order to make the measure work for quarters before and after the current quarter. Below is working:

WorkingDays before todayQ:= VAR _date = TODAY()
var last=DATEVALUE([SelectedEndDate])
var _result=
SWITCH(TRUE(),
MAX('Date'[QuarterCat])="After",0,
MAX('Date'[QuarterCat])="Before",
CALCULATE(
DISTINCTCOUNT('Date'[Date]),
FILTER(
ALL('Date'),
'Date'[Year Quarter Number] = MAX('Date'[Year Quarter Number]) &&
'Date'[Date] < DATE(YEAR(_date), MONTH(_date), DAY(_date)) &&
'Date'[Date] <= last &&
'Date'[IsWorkDayKey] = 1
)
),

CALCULATE(
DISTINCTCOUNT('Date'[Date]),
FILTER(ALL('Date'),
'Date'[Date] <= last &&
'Date'[Year Quarter Number] = MAX('Date'[Year Quarter Number]) &&
'Date'[Date] < DATE(YEAR(_date), MONTH(_date), DAY(_date)) &&
'Date'[IsWorkDayKey] = 1
)
)) return _result

All the best,

Helen

[bhanu_gautam]

@HLVW , Try using below measure

To create a measure that shows the number of past working days in the current quarter, you can modify your existing measure by incorporating the quarter information from your date table. 

WorkingDays before today (Quarter):=
VAR _date = TODAY()
VAR _quarter = SELECTEDVALUE('Date'[Quarter Name])
RETURN
CALCULATE(
DISTINCTCOUNT('Date'[Date]),
FILTER(
ALL('Date'),
'Date'[Date] < DATE(YEAR(_date), MONTH(_date), DAY(_date))
&& 'Date'[Date] >= DATE(YEAR(_date), MONTH(_date), 1)
&& 'Date'[IsWorkDayKey] = 1
&& 'Date'[Quarter Name] = _quarter
)
)

[HLVW]

Hi @bhanu_gautam 

Thanks a lot for your reply. 

I tried your suggestion, but when I filter on the month of May I still get 5 working days (May) instead of 26 days (May & April). I assume I should disregard days in some way?