I want to replace the line chart to variance chart by month

[Anonymous]

my data is graphed in line chart but i want to replace it with varience chart to show to number change between month. But i could not find a proper formular or fuction to handle this. So the i could see the acutal month - previous month changed.

Thanks in advance.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try below :

My table:

Calender:

EWS_Result:

Model:

Try below DAX:

Hit Number Variance =
VAR cur_month =
    SELECTEDVALUE ( Calender[Month] )
VAR cur_count =
    COUNT ( EWS_Result[Trigger_Type] )
VAR pre_count =
    CALCULATE (
        COUNT ( EWS_Result[Trigger_Type] ),
        Calender[Month] = cur_month - 1
    )
RETURN
    SWITCH ( TRUE (), cur_month - 1 = 0, cur_count, cur_count - pre_count )

Please refer below attached .pbix file

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Anonymous 
Please try

Hit Number Variance =
VAR CurrentDate =
    MAX ( TableName[Date] )
VAR CurrentMonth =
    MONTH ( CurrentDate )
VAR CurrentValue =
    SUM ( TableName[Hit Number] )
VAR PreviousValue =
    CALCULATE (
        SUM ( TableName[Hit Number] ),
        TableName[Date].[MONTH] = CurrentMonth - 1
    )
RETURN
    CurrentValue - PreviousValue

[Anonymous]

@tamerj1  

I try it but it trough an error on month. i think i send you more detail about my table.

My goal = i count the triggers (because it a string) and plot it by month. i think my problem would be related my Calender.

Thanks in advance and for your time.

[tamerj1]

Hi @Anonymous 
1. Use COUNTA instead of COUNT
2. Use [Month_Number] column instead of [Date].[Month]