Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rgsingh123
Regular Visitor

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

 

 

 

MonthTotal 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 

 

MonthTotal 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                                                 -  
2 ACCEPTED SOLUTIONS
v-yulgu-msft
Employee
Employee

Hi @rgsingh123,

 

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

1.PNG

 

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 ()
)

2.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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?

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

Hi @rgsingh123,

 

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

1.PNG

 

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 ()
)

2.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.