Forum Discussion

ghost10's avatar
ghost10
Frequent Visitor
3 years ago
Solved

Exclude Holidays and Retrieve Previous day Value

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.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

     

    expected result measure: =
VAR _prevworkingdate =
    MAXX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] < MAX ( 'Calendar'[Date] )
                && 'Calendar'[holidays_ind] = "False"
                && 'Calendar'[weekend_ind] = "False"
        ),
        'Calendar'[Date]
    )
RETURN
    CALCULATE (
        SUM ( 'FactTable'[Quantity] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _prevworkingdate )
    )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

     

    expected result measure: =
VAR _prevworkingdate =
    MAXX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] < MAX ( 'Calendar'[Date] )
                && 'Calendar'[holidays_ind] = "False"
                && 'Calendar'[weekend_ind] = "False"
        ),
        'Calendar'[Date]
    )
RETURN
    CALCULATE (
        SUM ( 'FactTable'[Quantity] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _prevworkingdate )
    )
  • v-yinliw-msft's avatar
    v-yinliw-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi ghost10 ,

     

    Could you please provide some sample data for me to test?

    Please remove sensitive data.

     

    Best Regards,

    Community Support Team _Yinliw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.