Forum Discussion
Previous Months MTD
Hello,
I have a line graph were I want compare today´s day* MTD, with previous months MTD of the same day (example until day 8). It´s "easy" to do the calculation LMTD or LYTD (seen as the % on the graph), but when I do calculation to return the measure on the graph I get the previous end of the months MTD .
*(actually it´s the max day were we had sales)
Hi Ricky_Pimenta ,
Sounds like you want get the values from the same day in last month unless the whole month. Maybe you can try DATEADD() function like the following code:
Last month = CALCULATE( SUM('Table’[values]), all(),DATEADD('Table'[date],-1,month))
or
Last month = CALCULATE( SUM('Table'[values]),FILTER(ALL('Table'),[date]=DATE(YEAR(MAX('Table'[date])) ,MONTH(MAX('Table'[date]))-1,DAY(MAX('Table'[date])))
Please share your pbix file without sensitive data and expect result, if you need more help.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello to All,
I resolved the issue, nonetheless I had to do workaround.
First I made a calculated Column in the Date Table(Dcalendario) which finds the Max of the day of the the date "sales"table (F_Consultas) and returns 1 or 0 if the date in the calendar day is <= of the selection.
Note:( ATD CE is the measure used)
Dia F_Consultas = IF(Dcalendario[Day] <= DAY(Max( F_Consultas[DATA])),1,0) - (Calculated Column)
After that I made my calculations to get the value:
ATD MTD All Previous Months = CALCULATE(TOTALMTD([ATD CE],Dcalendario[Date]), Dcalendario[Dia F_Consultas]=1)
this returns me the "sales" amount for all the previous until a specific day.
Being my last available date the 16 of March , it compares the MTD of March = 4.835 with the all the previous MTD of all Months of All Years until day 16. this allows me to compare in the graph or table not only LMTD or LYTD but also other months,
furthermore I made two additional measures (with the help of DNA Enterprise Videos) for the Highest Previous MTD
Highest Previous ATDCE MTD =
VAR Maxdias = Max( Dcalendario[Date])
VAR Dia = DAY(Maxdias)
VAR MonthC = Month(Maxdias)-1
VAR AnoC = Year(Maxdias)
VAR Calculo = CALCULATE( [Atd CE MTD], TOPN(1, FILTER( SUMMARIZE( ALL( Dcalendario ), Dcalendario[Date], Dcalendario[YearMonthShort], Dcalendario[YearMonthnumber], Dcalendario[Dia F_Consultas]), Dcalendario[Date] <= DATE(Anoc,MonthC,Dia) && Dcalendario[Dia F_Consultas]=1), [Atd CE MTD], DESC))
Return Calculo
So I have the comparison with the highest Previous MTD until the 16 .
To get what Month and Year the highest previous MTD refers to I made the measure:
Max MTD CE Month and Year = VAR Maxdias = Max( Dcalendario[Date]) VAR Dia = DAY(Maxdias) VAR MonthC = Month(Maxdias)-1 VAR AnoC = Year(Maxdias)
VAR ANO = CALCULATE( MAXX( TOPN(1, SUMMARIZE(Dcalendario,Dcalendario[Year],Dcalendario[Dia F_Consultas], "@Atd MTD", [Atd CE MTD]),[Atd CE MTD]), Dcalendario[Year]), ALL(Dcalendario), Dcalendario[Date] <= DATE(Anoc,MonthC,Dia) && Dcalendario[Dia F_Consultas]=1)
VAR Mes = CALCULATE( MAXX( TOPN(1, SUMMARIZE(Dcalendario,Dcalendario[MonthNameShort],Dcalendario[Dia F_Consultas], "@Atd MTD", [Atd CE MTD]),[Atd CE MTD]), Dcalendario[MonthNameShort]), ALL(Dcalendario), Dcalendario[Date] <= DATE(Anoc,MonthC,Dia) && Dcalendario[Dia F_Consultas]=1)
Return Mes & " " & Ano
I am certain that the solution that I found is not the most efficient or the "best practice" , but at this moment with the DAX knowledge I have it was best I came up to.
Thank you.
5 Replies
- amitchandak
Super User
Ricky_Pimenta , You can try measure like these with date table
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.Hello Again,
Maybe my explanation was not clear (or I did not understand your response). So I added the matrix.
My last day of sales was 9 March of 2022 =1.434, I would like to compare this to the value of MTD on 9 of January 2022 =1.232 (not visible in the table) and on the 9 February 2022 =1.782.
But the values returned are of 31 of January = 5288, 28 of February = 5131 compared to 9 of March = 1.434 as mentioned before.
Additionally in future months I will always want to do the same comparation (the day will of course change).
I would like to do this calculation in DAX to be dynamic and not with filters in the report
- v-chenwuz-msft
Community Support
Hi Ricky_Pimenta ,
Sounds like you want get the values from the same day in last month unless the whole month. Maybe you can try DATEADD() function like the following code:
Last month = CALCULATE( SUM('Table’[values]), all(),DATEADD('Table'[date],-1,month))
or
Last month = CALCULATE( SUM('Table'[values]),FILTER(ALL('Table'),[date]=DATE(YEAR(MAX('Table'[date])) ,MONTH(MAX('Table'[date]))-1,DAY(MAX('Table'[date])))
Please share your pbix file without sensitive data and expect result, if you need more help.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello to All,
I resolved the issue, nonetheless I had to do workaround.
First I made a calculated Column in the Date Table(Dcalendario) which finds the Max of the day of the the date "sales"table (F_Consultas) and returns 1 or 0 if the date in the calendar day is <= of the selection.
Note:( ATD CE is the measure used)
Dia F_Consultas = IF(Dcalendario[Day] <= DAY(Max( F_Consultas[DATA])),1,0) - (Calculated Column)
After that I made my calculations to get the value:
ATD MTD All Previous Months = CALCULATE(TOTALMTD([ATD CE],Dcalendario[Date]), Dcalendario[Dia F_Consultas]=1)
this returns me the "sales" amount for all the previous until a specific day.
Being my last available date the 16 of March , it compares the MTD of March = 4.835 with the all the previous MTD of all Months of All Years until day 16. this allows me to compare in the graph or table not only LMTD or LYTD but also other months,
furthermore I made two additional measures (with the help of DNA Enterprise Videos) for the Highest Previous MTD
Highest Previous ATDCE MTD =
VAR Maxdias = Max( Dcalendario[Date])
VAR Dia = DAY(Maxdias)
VAR MonthC = Month(Maxdias)-1
VAR AnoC = Year(Maxdias)
VAR Calculo = CALCULATE( [Atd CE MTD], TOPN(1, FILTER( SUMMARIZE( ALL( Dcalendario ), Dcalendario[Date], Dcalendario[YearMonthShort], Dcalendario[YearMonthnumber], Dcalendario[Dia F_Consultas]), Dcalendario[Date] <= DATE(Anoc,MonthC,Dia) && Dcalendario[Dia F_Consultas]=1), [Atd CE MTD], DESC))
Return Calculo
So I have the comparison with the highest Previous MTD until the 16 .
To get what Month and Year the highest previous MTD refers to I made the measure:
Max MTD CE Month and Year = VAR Maxdias = Max( Dcalendario[Date]) VAR Dia = DAY(Maxdias) VAR MonthC = Month(Maxdias)-1 VAR AnoC = Year(Maxdias)
VAR ANO = CALCULATE( MAXX( TOPN(1, SUMMARIZE(Dcalendario,Dcalendario[Year],Dcalendario[Dia F_Consultas], "@Atd MTD", [Atd CE MTD]),[Atd CE MTD]), Dcalendario[Year]), ALL(Dcalendario), Dcalendario[Date] <= DATE(Anoc,MonthC,Dia) && Dcalendario[Dia F_Consultas]=1)
VAR Mes = CALCULATE( MAXX( TOPN(1, SUMMARIZE(Dcalendario,Dcalendario[MonthNameShort],Dcalendario[Dia F_Consultas], "@Atd MTD", [Atd CE MTD]),[Atd CE MTD]), Dcalendario[MonthNameShort]), ALL(Dcalendario), Dcalendario[Date] <= DATE(Anoc,MonthC,Dia) && Dcalendario[Dia F_Consultas]=1)
Return Mes & " " & Ano
I am certain that the solution that I found is not the most efficient or the "best practice" , but at this moment with the DAX knowledge I have it was best I came up to.
Thank you.
hii Ricky_Pimenta
please use this dax, I re-write this code from @amitchandak
var _max = date(year(today()),month(today())-1,day(today())-1) var _min = eomonth(_max,-1)+1 return CALCULATE(sum['amazon',[purchase date] ,DATESBETWEEN('calendar'[Date],_min,_max))
