Previous month calculated measures

[yforti]

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.

[V-lianl-msft]

Hi @yforti ,

Is this problem sloved?
If not, please let me know.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yforti]

@amitchandak 

I created a time table as you can see: 

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]

Hi @yforti ,

Create a Calendar table.
Create the following calculated column to find out the month start date for each transaction date.
Create a relationship between the Date columns of Calendar table and StarDate of fact table.
Create a measure which will find the previous month's sum.

PrevMonthSum = CALCULATE(  
   SUM('Table'[value]),  
   DATEADD('Table 2'[Date].[Date], -1, MONTH)  
)  

Previous2MonthSum = CALCULATE(  
   SUM('Table'[value]),  
   DATEADD('Table 2'[Date].[Date], -2, MONTH)  
) 

sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@yforti , You can use time intelligence with date table,

Refer examples

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-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

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.