Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Use Measure with previous workday

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.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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 )
    )

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.