Power BI Previous Month Column showing blank

[PeraZo]

Hello, Power BI Community

I have a Data set like below.

I want to show Previous month value for each state (In this case WA and NY) so I wrote below DAX.

Previousmonth =

CALCULATE(SUM('Sheet1'[Sale]),PREVIOUSMONTH('Sheet1'[YearMonth]))

But the result is showing blank...

Could someone please help me understand how I can make this possible?

[Ahmedx]

or try this

[Ahmedx]

pls try this

[Ashish_Mathur]

Hi,

You should first of all create a Date Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Write your formula as a measure (not as a calculated colummn).  Furthermore, revise that measure to:

Previousmonth = CALCULATE(SUM('Sheet1'[Sale]),PREVIOUSMONTH(Calendar[Date]))

Hope this helps.

[jpessoa8 · Accepted solution]

Hi @PeraZo ,

The usage of PREVIOUSMONTH requires the Filter context and, for that reason should be used in a measure instead of a calculated column.

If, do to some business logic, you really need to create a calculated column for this, you should do:

var PreviousDate = IF( MONTH('Sheet1'[YearMonth]) = 1 , 
                        //if the previous month is last year
                        DATE(YEAR('Sheet1'[YearMonth])-1,MONTH('Sheet1'[YearMonth])-1,1), 
                        //if the previous month is this year
                        DATE(YEAR('Sheet1'[YearMonth]),MONTH('Sheet1'[YearMonth])-1,1)
                    )
return CALCULATE(SUM('Sheet1'[Sale]),FILTER('Sheet1', 'Sheet1'[YearMonth] = PreviousDate))

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa