Previous month calculated measures

Question

I need to calculate the total of the (VlConta A Vencer) column for the two months prior to the current month. The current month's data doesn't matter in my model. For example, we are in the month of January, I need to calculate the total of the column VL for the month of December and November, preferably in separate measures.

PS: The measures must be dynamic, because as soon as the month of January closes, the measures must be calculated for the months of December and January and so on.

amitchandak · Answer

yforti , You can use time intelligence with date table,
&nbsp;
Refer examples
&nbsp;
&nbsp;
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
Power BI — Month on Month with or Without Time Intelligence https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090ehttps://www.youtube.com/watch?v=6LUBbvcxtKA
&nbsp;
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.

v-lianl-msft · Answer

Hi yforti&nbsp;,&nbsp;Create a Calendar table.Create the following calculated column to find out the month start date for each transaction date.Create a&nbsp;relationship between the Date columns of Calendar table and StarDate of fact table.Create a measure which will find the previous month's sum.&nbsp;&nbsp;PrevMonthSum = CALCULATE(  
   SUM('Table'[value]),  
   DATEADD('Table 2'[Date].[Date], -1, MONTH)  
)

Previous2MonthSum = CALCULATE(  
   SUM('Table'[value]),  
   DATEADD('Table 2'[Date].[Date], -2, MONTH)  
) &nbsp;&nbsp;sample .pbix&nbsp;Best Regards,LiangIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

yforti · Answer

amitchandak&nbsp;I created a time table as you can see:&nbsp;calendario = CALENDAR(DATE(2007,1,1),DATE(2025,1,1))After that i created a relationship between the Calendario(date) table with the DT_ENTRADA in my database, see below:The problem is that I'm not getting any results, for example:I have a sales measure that is:vl conta = CALCULATE(SUMX('visao_etapa_conta - Não Remetidas','visao_etapa_conta - Não Remetidas'[VL_CONTA])), see the result WITHOUT having any relationship with the calendar table:See the result after creating the relationship between the tables:Why is the result blank when using the time table date?

v-lianl-msft · Answer

Hi yforti&nbsp;,&nbsp;Is this problem sloved?If not, please let me know.&nbsp;Best Regards,LiangIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.