Forum Discussion
Filtering Table based on if condition
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:
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 🙂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)))
4 Replies
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 , refer to my blog.
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
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.⚡
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)))
