Difference based on month

[rgsingh123]

Hi,

In the below table we have total GLP and month. Total GLP is aggregated value for the month of Aug-13 and so on.

Want a column with the difference between  Total GLP value based on months. In excel we could have achieved this C2 = b3 - b2 for the distinct value of months. Can someone help me how to write a calculated column in DAX

Month	Total GLP
Aug-13	5,07,73,678
Aug-13	5,07,73,678
Sep-13	6,24,10,183
Sep-13	6,24,10,183
Sep-13	6,24,10,183
Oct-13	7,14,50,816
Oct-13	7,14,50,816
Oct-13	7,14,50,816
Oct-13	7,14,50,816
Oct-13	7,14,50,816
Oct-13	7,14,50,816
Nov-13	8,36,69,540
Nov-13	8,36,69,540
Nov-13	8,36,69,540
Nov-13	8,36,69,540

expected output is 

Month	Total GLP
Aug-13	1,16,36,505
Aug-13	-
Sep-13	-
Sep-13	90,40,633
Sep-13	-
Oct-13	-
Oct-13	-
Oct-13	-
Oct-13	-
Oct-13	1,22,18,724
Oct-13	-
Nov-13	-
Nov-13	-
Nov-13	-
Nov-13	1,87,40,132
Nov-13	-

[v-yulgu-msft · Accepted solution]

Hi @rgsingh123,

The [Month] column should be set to date type. In Query Editor mode, add an index column.

Create a calculated column with below formula.

Difference =
IF (
    'Monthly GLP'[Index]
        = CALCULATE (
            MIN ( 'Monthly GLP'[Index] ),
            ALLEXCEPT ( 'Monthly GLP', 'Monthly GLP'[Month] )
        ),
    CALCULATE (
        MAX ( 'Monthly GLP'[Total GLP] ),
        FILTER (
            'Monthly GLP',
            'Monthly GLP'[Month].[MonthNo]
                = EARLIER ( 'Monthly GLP'[Month].[MonthNo] ) + 1
        )
    )
        - 'Monthly GLP'[Total GLP],
    BLANK ()
)

Best regards,
Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @rgsingh123,

The [Month] column should be set to date type. In Query Editor mode, add an index column.

Create a calculated column with below formula.

Difference =
IF (
    'Monthly GLP'[Index]
        = CALCULATE (
            MIN ( 'Monthly GLP'[Index] ),
            ALLEXCEPT ( 'Monthly GLP', 'Monthly GLP'[Month] )
        ),
    CALCULATE (
        MAX ( 'Monthly GLP'[Total GLP] ),
        FILTER (
            'Monthly GLP',
            'Monthly GLP'[Month].[MonthNo]
                = EARLIER ( 'Monthly GLP'[Month].[MonthNo] ) + 1
        )
    )
        - 'Monthly GLP'[Total GLP],
    BLANK ()
)

Best regards,
Yuliana Gu

[rgsingh123 · Accepted solution]

Thanks it worked. 

I feel it's a little complex to write such big DAX queries for simple calculations like this. Do you recommend any good tutorial for DAX that will help me to master this?