Forum Discussion

ghost10's avatar
ghost10
Frequent Visitor
3 years ago
Solved

Retrieve previous day values excluding weekends and holidays

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.

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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