Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
MeterCategory | billing_group_name | 2022-08 | 2022-09 | 2022-10 | 2022-11 | 2022-12 | 2023-01 | 2023-02 |
Virtual Machines | test1 | 0 | 9034.945 | 34526.89 | 101607.8 | 80903.09 | 89313.7 | 35392.87 |
Logic Apps | test1 | 0 | 55.0803 | 167.0973 | 397.2241 | 372.472 | 143.2417 | 173.1204 |
Virtual Machines | test1 | 0 | 102.0059 | 318.8347 | 230.1427 | 0 | 0 | 0 |
API Management | test1 | 0 | 0 | 232.8 | 10425.07 | 3251.925 | 909.3749 | 3455.625 |
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:
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |