Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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.
Hi @Anonymous
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
@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
)
)
Proud to be a Super User! |
|
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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |