Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a question regarding making my measure more intelligent. I don't know if this is possible.
I have a simple measure:
Count of new files = calculate(DISTINCTCOUNT(Dossier[id Dossier]), USERELATIONSHIP(calender[Date], Dossier[Status - Date created]))
I now want the measure to take into account weekdays and weekend days. I want to add up the count if id Dossier to the next weekday, so that will be always monday.
Is that possible to add into this measure?
Solved! Go to Solution.
Hi @Anonymous ,
I think you want to count values in weekend on Monday. I create a sample to have a test.
Firstly, I ceate a calendar table as below.
calender =
ADDCOLUMNS (
CALENDAR (
MIN ( 'Dossier'[Status - Date created] ),
MAX ( 'Dossier'[Status - Date created] )
),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"WeekStart",
[Date] - WEEKDAY ( [Date], 2 ) + 1
)
Relationship:
Measure:
Count of new files =
VAR _BASIC_COUNT =
CALCULATE (
DISTINCTCOUNT ( Dossier[id Dossier] ),
USERELATIONSHIP ( calender[Date], Dossier[Status - Date created] )
)
VAR _COUNT_THREE_DAYS =
CALCULATE (
DISTINCTCOUNT ( Dossier[id Dossier] ),
FILTER (
ALL ( Dossier ),
Dossier[Status - Date created]
>= MAX ( calender[Date] ) - 2
&& Dossier[Status - Date created] <= MAX ( calender[Date] )
)
)
RETURN
IF (
WEEKDAY ( MAX ( calender[Date] ) ) IN { 6, 7 },
BLANK (),
IF (
MAX ( calender[Date] ) = MAX ( calender[WeekStart] ),
_COUNT_THREE_DAYS,
_BASIC_COUNT
)
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
I think you want to count values in weekend on Monday. I create a sample to have a test.
Firstly, I ceate a calendar table as below.
calender =
ADDCOLUMNS (
CALENDAR (
MIN ( 'Dossier'[Status - Date created] ),
MAX ( 'Dossier'[Status - Date created] )
),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"WeekStart",
[Date] - WEEKDAY ( [Date], 2 ) + 1
)
Relationship:
Measure:
Count of new files =
VAR _BASIC_COUNT =
CALCULATE (
DISTINCTCOUNT ( Dossier[id Dossier] ),
USERELATIONSHIP ( calender[Date], Dossier[Status - Date created] )
)
VAR _COUNT_THREE_DAYS =
CALCULATE (
DISTINCTCOUNT ( Dossier[id Dossier] ),
FILTER (
ALL ( Dossier ),
Dossier[Status - Date created]
>= MAX ( calender[Date] ) - 2
&& Dossier[Status - Date created] <= MAX ( calender[Date] )
)
)
RETURN
IF (
WEEKDAY ( MAX ( calender[Date] ) ) IN { 6, 7 },
BLANK (),
IF (
MAX ( calender[Date] ) = MAX ( calender[WeekStart] ),
_COUNT_THREE_DAYS,
_BASIC_COUNT
)
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That helped, thanks a lot!
@Anonymous , Please refer to my blog on that
Traveling Across Workdays - What is the next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |