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
Georgia_H
Helper I
Helper I

Need help on deriving delta to compare current_mth vs previous month and on all historical months

Hello,

I need help on how to derive the delta between current_mth vs prev_mth and etc using DAX.

E.g: 2023-02 - 2023-01 ,   2023-01 - 2022-12,   2022-12 - 2022-11 

I am currently populating it manually in Power Query.

My sample data looks like this:

MeterCategorybilling_group_name2022-082022-092022-102022-112022-122023-012023-02
Virtual Machinestest109034.94534526.89101607.880903.0989313.735392.87
Logic Appstest1055.0803167.0973397.2241372.472143.2417173.1204
Virtual Machinestest10102.0059318.8347230.1427000
API Managementtest100232.810425.073251.925909.37493455.625
1 REPLY 1
MAwwad
Super User
Super User

 

You can use DAX to derive the delta between the current month and the previous month, as well as between all historical months. Here's an example formula:

 

 
Delta = VAR CurrentMonth = MAX('YourTable'[Date]) VAR PreviousMonth = CALCULATE(MAX('YourTable'[Date]), PREVIOUSMONTH('YourTable'[Date])) RETURN IF(ISBLANK(PreviousMonth), BLANK(), SUM('YourTable'[Value]) - CALCULATE(SUM('YourTable'[Value]), 'YourTable'[Date] = PreviousMonth))
 

This formula calculates the delta for each row in the table based on the Value column. You can replace 'YourTable' and 'Value' with the appropriate names from your data.

The formula first defines two variables CurrentMonth and PreviousMonth to hold the dates for the current month and the previous month, respectively. It then checks if PreviousMonth is blank, which could happen if there is no data for the previous month. If PreviousMonth is not blank, it calculates the sum of the Value column for the current month and subtracts the sum of the Value column for the previous month.

You can use this formula in a calculated column or measure depending on your use case. If you want to calculate the delta for all historical months, you can modify the formula to use a date table and iterate over all months using a CALENDAR function.

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.

Top Solution Authors