topic Previous month of last month in dataset in DAX Commands and Tips

Previous month of last month in dataset

mariahMC — Tue, 22 Oct 2024 22:10:48 GMT

I am trying to create a KPI visual that shows the sum of the last month in the dataset compared to the sum of the previous month (of the last month in the data set). To do this, I created a measure to calculate the previous month total:

PreviousMonth = CALCULATE(SUM('CompPayments'[Payment Amount]), PREVIOUSMONTH('CompPayments'[Payment Received Date].[Date]))

However, the KPI card shows blank because it's calculating based on current month but I will never have data for the current month.

For example, we are in October but the last month in my dataset is September so I need it to show total of September with the target "goal" being the sum of August. When November hits and I update the dataset, I need it to show October vs September, etc.

Re: Previous month of last month in dataset

Anonymous — Wed, 23 Oct 2024 02:29:31 GMT

Hi mariahMC ,

Please update the formula of measure [PreviousMonth] as below and check if it can return the expected result...

PreviousMonth = CALCULATE ( SUM ( 'CompPayments'[Payment Amount] ), PREVIOUSMONTH ( 'CompPayments'[Payment Received Date] ) )

Best Regards

Re: Previous month of last month in dataset

Jihwan_Kim — Wed, 23 Oct 2024 04:59:09 GMT

Hi,

Without seeing your semantic model, I can't be entirely certain, but it seems likely that the latest month is being defined in your model using the TODAY() function in a measure.

If that’s the case, I recommend using the last date from the fact table instead of TODAY() to define the latest month. This way, the latest month will always display the data (no blank), allowing you to compare it with the previous month.

Hope this helps!

Re: Previous month of last month in dataset

mariahMC — Wed, 23 Oct 2024 15:56:10 GMT

I made this change and it gives me September total, but the Goal is blank and not calculating the total of August

Re: Previous month of last month in dataset

mariahMC — Wed, 23 Oct 2024 16:00:45 GMT

Right now I am only using one data source in my model as practice since I am new to power bi. Eventually I will add additional sources to my model. Do I need to create a date table?

Re: Previous month of last month in dataset

Jihwan_Kim — Wed, 23 Oct 2024 16:14:05 GMT

Hi,

I suggest to create a date table.

Re: Previous month of last month in dataset

mariahMC — Mon, 28 Oct 2024 14:13:02 GMT

Thanks! I created a date table and filtered by maxdate and that seems to work for now.

PrevMTD =

var maxdate = EOMONTH(TODAY(),-2)

RETURN

CALCULATE(

SUM('CompPayments'[Payment Amount]),

FILTER(ALL('Calendar'),'Calendar'[Date] <= maxdate),

DATEADD('Calendar'[Date],-1,MONTH))