Use Measure with previous workday

[Anonymous]

I have a simple measure:

Orders released not shipped = 

CALCULATE(
    COUNT(CD_OTTC_SHIPPING_LINE[KeyID]); CD_OTTC_SHIPPING[ISBLANK_DISPATCH_DATE] = TRUE
) 

I need this KPI for each day and the previous workday. I have a table with all previous workdays. Same day works fine. I thought i can just call this measure with my previous workday instead but dont know how to do it. It is supposed to be like the last image. Using PREVIOUSDAY() does not help since it can not take care of Holidays and weekends.

[Anonymous]

Hi @Anonymous ,

Please try to use following measure formula if it suitable for your requirement:

Measure =
VAR currDate =
    MAX ( 'Table'[Date] )
VAR prev =
    IF (
        WEEKDAY ( currDate, 2 ) <= 5,
        IF ( WEEKDAY ( currDate, 2 ) <> 1, currDate - 1, currDate - 3 )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] = prev )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

That is considering workdays and it works well! But i still have the problem with my holidays. There is no way to calculate a holiday unfortunately. I have Source with all holidays necessary. You have any suggestion on how to do this?

I came up with following idea.

Orders released previous workday not shipped = 
CALCULATE(
    [Orders released not shipped];
        FILTER(ALLSELECTED(CD_OTTC_SHIPPING_LINE); CD_OTTC_SHIPPING_LINE[OLLASTUPDDTE] = VALUES(Workdays[previous workday]) 
        ) 
)

But it only works if i chosse a single day by slicer. I cant display it for each day on a table. There will be an Error: "A table of multiple values was supplied where a single value was expected" which makes sense but i thought if i have each Date in my Reporting table PBI would only find the one value in my "Prior workday" Column.

[Anonymous]

Basicly i have to FILTER my table so that i get each  row where

[OLLASTUPDDTE].[Date]

is equal to my "previous workday". My report table would contain a current date 2019-04-23 whre my "previous workday" is the 2019-04-18 because in Germany 19-22 is a holiday. So i need all entries from my table where [OLLASTUPDDTE].[Date] = 2019-04-18. There will be several entries for this date. I could then use my Measure:

[Orders released not shipped]

Result should be my current 2019-04-23  date with my Counted rows from 2019-04-18.

[Anonymous · Accepted solution]

I played around a little bit with your code and came up with this solution. Seems to work now.

Test previous wd = 
VAR currDate =
    MAX (DimDate[Date])
VAR prev =
    MAX(Workdays[previous workday])
    
RETURN
    CALCULATE (
        [Orders released not shipped];
        FILTER(ALLSELECTED(CD_OTTC_SHIPPING_LINE); CD_OTTC_SHIPPING_LINE[date.OLLASTUPDDTE] = prev )
    )

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also, I have another Quick Measure in there called Net Work Days that shows how to handle holidays and weekends.