MAT, YTD and Month for previous period

[tomtommaxi]

Hello everyone.

I'm stuck on a problem for almost 2 weeks so I decided to ask some help here. I really hope someone can help me!

I have created a report with 2 slicers. One where I can chose a month and year between 202001 and 202212, and the other one permit to chose between MAT, Month or YTD (MAT=12 previous months from the month selected, YTD=from January to the month selected, Month = only the month selected). 

I created a Measure, that permit to display the values of the period that the user is selecting:

Measure = 
 IF(MAX('Table'[Selection])= "YTD", 
                    CALCULATE(SUM('Fact table'[Value]) , FILTER(ALL('Table Dates') , 'Table Dates'[Year] = SELECTEDVALUE('Table Dates'[Year]) && 'Table Dates'[Month]<= SELECTEDVALUE( 'Table Dates'[Month]))), 
         IF(MAX('Table'[Selection])= "MAT" , 
                    CALCULATE(SUM('Fact table'[Value]) , FILTER(ALL('Table Dates') , 'Table Dates'[rank]<= MAX('Table Dates'[rank]) && 'Table Dates'[rank]> MAX('Table Dates'[rank])-12)), SUM('Fact table'[Value])))

I need to create a new measure, my goal is to show the value of the same period last year. 

For exemple if the user is selecting "MAT" and "202205", it will show the value of 12 months from 202105 to 202006.

I wrote this measure for the moment, it works when I select "YTD" but when I select "MAT" or "Month" it's not working, it calculates wrong values.

Here my measure for previous period :

Period -1 = 
IF(MAX('Table'[Selection])= "YTD",
CALCULATE(SUM('Fact table'[Value]) , FILTER(ALL('Table Dates') , 'Table Dates'[Year] = SELECTEDVALUE('Table Dates'[Year]) - 1 && 'Table Dates'[Month]<= SELECTEDVALUE( 'Table Dates'[Month]))),
IF(MAX('Table'[Selection])= "MAT" ,
CALCULATE(SUM('Fact table'[Value]) , FILTER(ALL('Table Dates') , 'Table Dates'[rank]<= MAX('Table Dates'[rank]) - 12 && 'Table Dates'[rank]> MAX('Table Dates'[rank])-24 && 'Table Dates'[Year] = SELECTEDVALUE('Table Dates'[Year]) - 1)),
SUM('Fact table'[Value])))

Can someone help me with this measure to have the good Month and MAT ? Here is the file if you want to see how I built it :

https://drive.google.com/file/d/17yFgZmgaSejfdkFj90v4Zt3vs8m4rRKK/view?usp=sharing

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@tomtommaxi , use date table and time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , MAXX(allselecetd('Date'), 'Date'[Date]))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , MAXX(allselecetd('Date'), 'Date'[Date]))
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA