Forum Discussion
Use Measure with previous workday
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 ) )
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.
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.
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 ) )