cancel
Showing results for
Did you mean:
Frequent Visitor

## previous month amount is blank

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
1 ACCEPTED SOLUTION
Community Support

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:

Best Regard

Lucien Wang

4 REPLIES 4
Community Support

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:

Best Regard

Lucien Wang

Memorable Member

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

Frequent Visitor

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)

Memorable Member

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

Regards,
Nemanja Andic