previous month amount is blank

[sneevand]

Hi Guys,

I used below dax to get previous sales amount; but i get blank amount

SalesPreviousMonth = CALCULATE(sum(Invoice[SaleAmount]),PREVIOUSMONTH(DimDate[Date]))

My invoice to dim date is many to 1 relationship

How to get previous month data?

Regards,

Navin

[v-luwang-msft · Accepted solution]

Hi @sneevand ,

You also could use the following measure:

Step 1, create a new column:

last month = DATEADD(Invoice[MonthValue],-1,MONTH)

Step 2 ,use the following dax to create a measure:

salelastmonth =
VAR last1 =
    CALCULATE (
        MAX ( Invoice[saleamount] ),
        FILTER ( ALL ( Invoice ), Invoice[MonthValue] = MAX ( Invoice[last month] ) )
    )
VAR last2 =
    IF ( MAX ( Invoice[last month] ) = BLANK (), "first month", last1 )
RETURN
    last2

final you will get below:

Click here to download pbix if you need.

Best Regard

Lucien Wang

[nandic]

Hi @sneevand ,

You calculation is correct. You might try to mark your date table as Date table or to check calculations on this link:
https://community.powerbi.com/t5/Desktop/Previous-Month-show-blank/m-p/1401555 

If it doesn't help, could you share the file (create dummy data, but keep the structure - tables, relationships, calculations).

Regards,
Nemanja Andic

[sneevand]

Hi @nandic 

below approach worked but on every year first month, Salespreviousmonth is blank 🙂

Salespreviousmonth =

var validmonth=MAX(Invoice[MonthValue])-1

return

CALCULATE(sum(Invoice[saleamount]),Invoice[MonthValue]=validmonth)

[nandic]

It should be empty only for first record, but not for each first month of the year.
Attached demo.

Regards,
Nemanja Andic