DAX Measure - Comparison of Monthly Volume Increase or Decrease

[Newbie22]

Hi! 

I've been a silent reader and this community have helped me with multiple solutions. However, this time I can't find the answer to my query.

First of all, I need to create a DAX comparison of volume per month (If the volume decreased or increased from the previous month) then I will have to create an icon using conditional formatting.

Example of format in Excel:

My Table in Power BI: Monthly Summary

Two Columns: Volume and Month

I'm using this measure but it's not working 😓

Condition_Volume =
VAR _val =
CALCULATE ( SUM ( 'Monthly Summary'[Volume] ) )
-
CALCULATE ( SUM ( 'Monthly Summary'[Volume] ),
PREVIOUSMONTH ( 'Monthly Summary'[Month] ) )

RETURN
SWITCH ( TRUE (),
_val < 0, -1,
_val = 0, 0,
_val > 0, 1 )

----------------------------

This is actually my first post here. I hope someone can help me. Thanks in advance!

----------------------------

I finally found the solution:

Sharing the code here in case someone needs it.

_Condition_Volume =

var monthly=sum('Monthly Summary'[Volume])

var Monthlyp= CALCULATE(SUM('Monthly Summary'[Volume]),CALCULATETABLE(DATEADD('Monthly Summary'[Date], -1,MONTH)))

var result=monthly-Monthlyp

var result2=Switch(True,

    result<0,-1,

    result=0,0,

    result>0,1)

return

result2

 Here's how it looks in my dashboard. You can finally see the decrease or increase comparison of volume each month 😊🙌

[Anonymous]

Hi Newbie22

Please try this measure to use afetr on the cond formatting.

This is the table I built to to it "Feuil1"

Here is the code

Variance =

var monthly=sum(Feuil1[Volume])

var Monthlyp=calculate([Monthly_volume],PREVIOUSMONTH('Date'[Date]))

var result=monthly-Monthlyp

var result2=Switch(True,

    result<0,-1,

    result=0,0,

    result>1,1)

return

result2

[Newbie22]

Hi JamesFr06,

Thanks for your reply.

I tried using your measure but I think I did something wrong?

This is the code:

_Condition_Volume =

var monthly=sum('Monthly Summary'[Volume])

var Monthlyp=calculate([Monthly_volume],PREVIOUSMONTH('Monthly Summary'[Month]))

var result=monthly-Monthlyp

var result2=Switch(True,

    result<0,-1,

    result=0,0,

    result>1,1)

return

result2

---------------------------------------------

 Note: I used below column for PREVIOUSMONTH

 -----------------------------------------

Here is the result when I visualize it using matrix:

March and April Volume should have red arrow since both volumes decreased. 

 Here's the screenshot of my conditional formatting for volume:

Please let me know if you needed more information. Thank you so much for the assistance. 😊

[Anonymous]

Hello @Newbie22

Thanks for your return. The only difference I see is on the calculation of previuos month. I worked with a date table and you you worked with a month column in the same table. Do you have one date table ? 

PREVIEW

[Newbie22]

Hi @Anonymous 

Yes, I have another Date table (Calendar). Tried using that too and it's still not working. 

This time it returned to 0 (based on my icon)

I also noticed that you have created another measure named "Monthly_Volume". May I know your calculation? That could be our difference..

Here's the screenshot of your DAX:

I really appreciate your help to me. 🤗

[Anonymous]

Hi @Newbie22 

Please find the table I had created and the measure.