Previous month in card showing blank

[Anonymous]

Hi

I'm trying to show a previous month value in a card but it shows the latest month

The measure i'm using works fine in a table so i'm a little bit stumped??

Measure = CALCULATE(sum(Nominal_Roll[FTE]), PREVIOUSMONTH(lastdate(Date_table[Date])))

The middle box of the below should be showing 2,225.45

Any help greatly appreciated!

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Sorry, I didn't think carefully enough. I have modified it:

Measure = 
var _a = MAX('Date_table'[Date])
var _b = EDATE(_a,-1)
return CALCULATE (
    SUM ( Nominal_Roll[FTE] ),
    FILTER (
        ALL('Nominal_Roll'),
            YEAR('Nominal_Roll'[Extract_date]) = YEAR(_b)&&
            MONTH('Nominal_Roll'[Extract_date])= MONTH ( _b )
    )
)

 Ouptup:

Best Regards,

Jianbo Li

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

[v-jianboli-msft]

Hi @Anonymous ,

Please try:

Measure = 
CALCULATE (
    SUM ( Nominal_Roll[FTE] ),
    FILTER (
        ALL('Nominal_Roll'),
            YEAR('Nominal_Roll'[Extract_date]) = YEAR(MAX('Date_table'[Date]))&&
            MONTH('Nominal_Roll'[Extract_date])= (MONTH ( MAX ( Date_table[Date] ) ) - 1)
    )
)

Output:

Best Regards,

Jianbo Li

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

[MeiJiang]

Hi Jianbo,

Your solution works for me in a card, but when I add one more slicer which is Hierarchy 1, the previuos month doesn’t change at all. For example, if I select Hierarchy 1 “B”, the previous month should be 13, but it still shows the whole month.. Can you help on this?

Please see the attched samples:

Previous Month.pbix

Sample Raw Data - Previous Month.xlsx

Thanks,

Mei

[Anonymous]

Many thanks for this, it still didn't work though unfortunately.

However if i change the "-1" to "-2" it gives me the correct answer which i don't quite understand.

One problem i can foresee with this formula though. I'm not sure it will work when we get to January?? I don't think the "-1" would give me Decembers figure?

[v-jianboli-msft]

Hi @Anonymous ,

I can't reproduce your issue in my side. Could you please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[Anonymous]

Here's a snapshot of my table below where the Extract_date is related to my date table

[v-jianboli-msft]

Hi @Anonymous ,

PREVIOUSMONTH function returns a table that contains a column of all dates from the previous month, based on the first date in the Dates column, in the current context.

The context of the card and the table is different. In this condition, the result is showing blank.

Please try something like this:

CALCULATE (
    SUM ( Nominal_Roll[FTE] ),
    FILTER (
        Date_table,
        MONTH ( Date_table[Date] )
            = MONTH ( MAX ( Date_table[Date] ) ) - 1
    )
)

Best Regards,

Jianbo Li

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

[Anonymous]

Thanks for the reply but unfortunately it's still returning blank