Retrieve previous day values excluding weekends and holidays

[ghost10]

Hi All,

I would need some direction on Additional logic on retrieving previous value. Requirement is to exclude Weekends and holidays  bring Friday Value over to Monday as Prev Value .

I already have weekend and holidays column as weekend_ind and holidays_ind.

[Anonymous · Accepted solution]

Hi @ghost10,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

To calculate the previous date values, you can try to use the following measure formulas:

formula =
VAR currDate =
    MAX ( Table[Date] )
VAR holidayList =
    VALUES ( Holiday[Date] )
VAR prevDate =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date] < currDate
                && WEEKDAY ( [Date], 2 ) <= 5
                && NOT ( [Date] IN holidayList )
        ),
        VALUES ( Table[Category] )
    )
RETURN
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER ( ALLSELECTED ( Table ), [Date] = prevDate ),
        VALUES ( Table[Category] )
    )

Regards,

Xiaoxin Sheng