topic DAX Measure - Comparison of Monthly Volume Increase or Decrease in DAX Commands and Tips

DAX Measure - Comparison of Monthly Volume Increase or Decrease

Newbie22 — Thu, 08 Sep 2022 15:10:36 GMT

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 😊🙌

Re: DAX Measure - Comparison of Monthly Volume Increase or Decrease

Anonymous — Tue, 23 Aug 2022 21:08:26 GMT

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

Re: DAX Measure - Comparison of Monthly Volume Increase or Decrease

Newbie22 — Wed, 24 Aug 2022 07:36:54 GMT

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. 😊

Re: DAX Measure - Comparison of Monthly Volume Increase or Decrease

Anonymous — Wed, 24 Aug 2022 08:11:22 GMT

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

Re: DAX Measure - Comparison of Monthly Volume Increase or Decrease

Newbie22 — Wed, 24 Aug 2022 12:40:44 GMT

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. 🤗

Re: DAX Measure - Comparison of Monthly Volume Increase or Decrease

Anonymous — Wed, 24 Aug 2022 12:58:24 GMT

Hi Newbie22

Please find the table I had created and the measure.