Show MoM variance in line chart

[vijayvizzu]

Hi, I have created a Line Chart visual, where X axis is "Month-Year", Y axis is values. I created couple of DAX measures and add those measures into Line Values. This is working fine, it shows me the trend of each measure values. But when i am trying to create a Month over month change variance, to show the each trend comparision with the previous month values, its not working as expected.

The variance should show intially the overall variance chart, and when i select any of the trend, then it should display the variance for that line only. I created this DAX measure to achieve this, but failed

=% Change = 
VAR CurrentValue = [ABC]+[DEF]+[GHI]+[JKL]+[MNO]
VAR PreviousValue = CALCULATE(CurrentValue, DATEADD(CalendarT[Date],-1,MONTH))
RETURN
DIVIDE(CurrentValue - PreviousValue, PreviousValue)

[Anonymous]

Hi @vijayvizzu,

Can you please share some dummy data to help us clarify your data structure and test coding formula on it?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[vijayvizzu]

@Anonymous  I am pasting the sample of the code, i had created similar six measures, its basically different mixing composition.

Thereafter, i created a line chart by adding these 6 measures to see the trend. Now i want to see the variance of each month.

ABC Blend-XYZ  = 
VAR ABC_XYZ _LLDPE = 
CALCULATE(
    [Avg USD]*0.65,
    FILTER(
        MAIN,
        MAIN[SRC_GRADE]="LLDPE-XYZ -SE ASIA"
    )
)
VAR ABC_XYZ _LDPE = 
CALCULATE(
    [Avg USD]*0.20,
    FILTER(
        MAIN,
        MAIN[SRC_GRADE]="LDPE-XYZ -SE ASIA" 
    )
)
VAR ABC_DEF_LLDPE_C6 = 
CALCULATE(
    [Avg USD]*0.14,
    FILTER(
        MAIN,
        MAIN[SRC_GRADE]="LLDPE C6-DEF-SE ASIA" 
    )
)
RETURN
ABC_XYZ_LLDPE+ABC_XYZ_LDPE+ABC_DEF_LLDPE_C6

[amitchandak]

@vijayvizzu , As these are complete month use datemtd

example

=% Change =
VAR CurrentValue = CALCULATE([ABC]+[DEF]+[GHI]+[JKL]+[MNO],DATESMTD(CalendarT[Date]))
VAR PreviousValue = CALCULATE([ABC]+[DEF]+[GHI]+[JKL]+[MNO],DATESMTD(dateadd(CalendarT[Date],-1,MONTH)))
RETURN
DIVIDE(CurrentValue - PreviousValue, PreviousValue)

simple date -1 month create problem sometime.

also, make sure calendarT is marked as a date table

Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

[vijayvizzu]

@amitchandak  I tried it, but its not working,

[Mariusz]

Hi @vijayvizzu 

When using CALCULATE:

• First argument has to be an agregation like SUM, MAX or if you expecting scelar value then SELECTED 
  VALUE, 
• You can not use variebles in the first argument as any further arguments 2, 3.... will not be applied.

Other than that your code looks ok.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn