Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Previous working day Total

I am trying to get a total for the previous working day total in my model    My data is structured as below  PO No Purch Date Quantity 100008 30-Sep-21 40 100010 1-Oct-21 15 1000...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    I updated your sample pbix file(see attachment), please check whether that is what you want.

    Previous Work Day Qty = 
VAR _curdate =
    SELECTEDVALUE ( 'Purch Fact'[Purch Date] )
VAR _preworkdate =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] < _curdate
                && WEEKDAY ( 'Calendar'[Date], 2 ) <= 5
        )
    )
RETURN
    IF (
        DAY ( _curdate ) = 1,
        [Total Qty],
        SUMX (
            FILTER ( ALLSELECTED ( 'Purch Fact' ), 'Purch Fact'[Purch Date] = _preworkdate ),
            [Total Qty]
        )
    ) + 0

     

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

Previous Work Day Qty = 
VAR _curdate =
    SELECTEDVALUE ( 'Purch Fact'[Purch Date] )
VAR _preworkdate =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] < _curdate
                && WEEKDAY ( 'Calendar'[Date], 2 ) <= 5
        )
    )
RETURN
    IF (
        DAY ( _curdate ) = 1,
        [Total Qty],
        SUMX (
            FILTER ( ALLSELECTED ( 'Purch Fact' ), 'Purch Fact'[Purch Date] = _preworkdate ),
            [Total Qty]
        )
    ) + 0

 

Best Regards