Dax Query with a Dateadd Measure as a filter

[Anonymous]

I am trying to create a measure which requires a min(dateadd) filter however it is not possible in Powerbi So i was wondering if there is another way to achieve this:

My current Measure:

Aged Wip 1 month= 
return CALCULATE([WIP Close],FILTER('Age Date','Age Date'[Age Date] <= Max('Tran Date'[Tran Date])
))

MY report has a period filter which is the (Tran Date). The measure above basically makes sure the Age Date is less than or equal to the Max Tran date (in the filter report pane).

However I need to make a measure which includes a filter to this "Aged Wip 1 Month" to say that the Age Date has to be >= TranDate - 1 month. This will give me all WIP Closed with an age date within 1 month of the max tran date.

Is this possible in powerbi? The business requires this in a measure form.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

For example, [tran date] is added into Report level filter, max tran date is 2020/3/15

if i use function below, it recorgnize that 2020/2/1~2020/3/15 is the period within one month of the max tran date.

max tran date = MAX('tran datedate'[tran date])

within 1 month = IF(MAX('Age date'[age date])<=[max tran date]&&DATEDIFF(MAX([age date]),[max tran date],MONTH)<=1,1,0)

Measure = CALCULATE(COUNT('Age date'[age date]),FILTER('Age date',[within 1 month]=1))

If your expected result is not like this, please let me know.

[Anonymous]

Thanks this worked perfectly, it takes 3 seconds to load but i guess that will have to do, i am not sure why datediff did not work previously.

Question: Actually i think it did not work before because my Max(Date) Query was in the measure and not build outside the measure as its own standalone measure. Does anybody Know why you need to have the Max(Date) Measure outside of the Measure. IE you cant have Var MaxDate = Max(Date) or just Max(Date) in the measure itself you have to create a seperate measure?

For others looking at this post i had t ochange the within 1 month to :

var within1month = IF([maxAge]<=[maxtrandate],if(DATEDIFF([maxAge],[maxtrandate],MONTH)=1,1,BLANK()),BLANK())

This is due to an error with the original of (Datediff start date cannot be greater than end date). Then just change the =1 to whatever the date diff is...

Edit: In order to get this working with my WIP measure:

1 month wip age = 

var within1month = IF([maxAge]<=[maxtran],if(DATEDIFF([maxAge],[maxtran],MONTH)=1,1,BLANK()),BLANK())

return CALCULATE([WIP Close], FILTER('Age Date','Age Date'[Age Date] <= Max('Tran Date'[Tran Date]) && if(DATEDIFF([maxAge],[maxtran],MONTH)=1,1,BLANK()) = 1))

Thanks a ton for this!

[amitchandak]

@Anonymous , I did not check the logic

But you have move max out if you need a max of slicer or date

Aged Wip 1 month= 
var _max =Maxx('Tran Date','Tran Date'[Tran Date])
return
return CALCULATE([WIP Close],FILTER('Age Date','Age Date'[Age Date] <= _max
))

[Anonymous]

I actually do not have problems with the Max inside of the calculate. IT is filtering down as expected.

So far i have :

Aged Wip Current =

CALCULATE([WIP Close], FILTER('Age Date',MONTH('Age Date'[Age Date]) = MONTH(max('Tran Date'[Tran Date]))-3),

FILTER('Age Date',YEAR('Age Date'[Age Date]) = YEAR(max('Tran Date'[Tran Date]))))

Where -3 Is the 3 Month Aged wip, then -4 for 4 month Aged Wip. However i am running into an issue with the End of the year... It breaks as soon as the month is less than january...