Previous month of last month in dataset

[mariahMC]

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.

[Jihwan_Kim]

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!

[mariahMC]

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))

[mariahMC]

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?

[Jihwan_Kim · Accepted solution]

Hi,

I suggest to create a date table.

[Anonymous]

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

[mariahMC]

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