Forum Discussion

Aahil10's avatar
Aahil10
Frequent Visitor
5 years ago

Filter MTD Query with custom date range

Hello! I have used the TOTALMTD and DATESMTD function in my measures but now need to figure out how to filter it to a custom date range for each month. The month's we use dont exactly follow the Cal...
Need Help
amitchandak's avatar
amitchandak
Super User
5 years ago

Aahil10 , I such case base on start date of period and end date of period create following columns in your date table

 

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense) 

//OR

Period Rank = RANKX(all('Period'),'Period'[period start date],,ASC,Dense) 

Period Day = DATEDIFF('Date'[Period Start Date],'Date'[Date],Day)+1


This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

 

PTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Period Day] <=max([Period Day])))
LPTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Period Day] <=max([Period Day])))