Forum Discussion

rgsingh123's avatar
rgsingh123
Regular Visitor
8 years ago
Solved

Difference based on month

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. I...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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's avatar
    rgsingh123
    8 years ago

    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?

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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's avatar
rgsingh123
Regular Visitor
8 years ago

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?