Forum Discussion
Calculated column for Relative Date with multiple conditions
Hi!!
I have a table in my PBI that includes columns for Item # and Planned Ship Date
I want to create a visual in my report (just a regular table) that shows items with a Planned Ship Date of -1 week and +4 weeks from TODAY (so could be showing different items every day)
I tried using a relative date filter but you can only do one condition at a time, and it wasn't recognizing it if I put in 2 different ones.
SO, I was thinking maybe there was a way to write a new column that was basically:
If Planned Ship Date is within -1 Week OR within +4 weeks from TODAYS DATE, write "Y", if not stay blank
Then I could use that as a visual filter for my table visual and only show rows with "Y" in it.
Is this possible?!?!? If so, can someone help me write this so it is syntatically correct?
Thank you in advance!!! :smileyvery-happy:
Hi sy898661 ,
In your scenario, we can use the following DAX query:
Measure = IF ( MIN ( Table1[Planned Ship Date] ) >= SELECTEDVALUE ( 'Table'[Date] ) - 7 && MIN ( Table1[Planned Ship Date] ) <= SELECTEDVALUE ( 'Table'[Date] ) + 28, "Y", BLANK () )Please refer to the following example, assume we have a table like below:
Then we need to create a calendar table for slicer, then create above measure. The result will like below:
Best Regards,
Teige
3 Replies
Hi sy898661 ,
In your scenario, we can use the following DAX query:
Measure = IF ( MIN ( Table1[Planned Ship Date] ) >= SELECTEDVALUE ( 'Table'[Date] ) - 7 && MIN ( Table1[Planned Ship Date] ) <= SELECTEDVALUE ( 'Table'[Date] ) + 28, "Y", BLANK () )Please refer to the following example, assume we have a table like below:
Then we need to create a calendar table for slicer, then create above measure. The result will like below:
Best Regards,
Teige
Also when I say TODAYS DATE I mean relatively, not just 7/10/2019. If we look at this in 2 months I want it to jusy show -1/+4 weeks from whatever date it currently is.
Just wanted to clear that up :)