Count of files which are in weekend on next working day

[Anonymous]

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?

[v-rzhou-msft · Accepted 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.

[Anonymous]

That helped, thanks a lot!

[amitchandak]

@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