Previous Months MTD

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.