cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sneevand
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
v-luwang-msft
Community Support
Community Support

Hi @sneevand ,

You also could use the following measure:

Step 1, create a new column:

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

v-luwang-msft_0-1614065836169.png

 

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:

v-luwang-msft_1-1614065836173.png

 

Click here to download pbix if you need.

 

Best Regard

Lucien Wang

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

Hi @sneevand ,

You also could use the following measure:

Step 1, create a new column:

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

v-luwang-msft_0-1614065836169.png

 

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:

v-luwang-msft_1-1614065836173.png

 

Click here to download pbix if you need.

 

Best Regard

Lucien Wang

nandic
Memorable Member
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

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)
 
 

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

 

previous month.PNG

Regards,
Nemanja Andic

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors