Filtering Table based on if condition

[SJ25]

Hello All,

I have a table with shipments data & want to sum the amount of shipments for the previous day. So if today is Monday, I want to filter the table for Friday (Today -3) but if today is any other day, I just want to filter for the day before (Today-1).

I tried the below DAX but it didn't work:

CALCULATE(SUM('Table1'[ShipmentAmount],FILTER('Table1',IF(WEEKDAY(TODAY(),2)=1, 'Table1'[ShipmentDate] = TODAY()-3,'Table1'[ShipmentDate]=TODAY()-1))))

I tried moving the If statement around in the formula but that didnt work either. Any help would be much appreciated.

Thanks

[nandukrishnavs · Accepted solution]

@SJ25 

Try something like this

MeasureVal = 
VAR _weekday =
    WEEKDAY ( TODAY (), 2 )
VAR _x =
    IF ( _weekday = 1, 3, 1 )
VAR _result =
    CALCULATE (
        SUM ( 'Table1'[ShipmentAmount] ),
        FILTER ( 'Table1', Table1[ShipmentDate] - _x )
    )
RETURN
    _result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[SJ25 · Accepted solution]

Thank you all for the quick answers. I was able to make it work using:

CALCULATE(SUM('Table1'[ShipmentAmount]),
FILTER('Table1',IF(WEEKDAY(TODAY(),2)-1, 'Table1'[ShipmentDate]-TODAY()-3, 'Table1'[ShipmentDate]-TODAY()-1)))

[amitchandak]

@SJ25 , refer to my blog.

https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

Create a rank on

Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])

and use that. I have shown usage of rank on [Work Date Cont],

Now -1 rank will yesterday which is a working day

[parry2k]

@SJ25 check this post

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[nandukrishnavs · Accepted solution]

@SJ25 

Try something like this

MeasureVal = 
VAR _weekday =
    WEEKDAY ( TODAY (), 2 )
VAR _x =
    IF ( _weekday = 1, 3, 1 )
VAR _result =
    CALCULATE (
        SUM ( 'Table1'[ShipmentAmount] ),
        FILTER ( 'Table1', Table1[ShipmentDate] - _x )
    )
RETURN
    _result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂