Forum Discussion
Measure for incremental calculation by multiple conditions
Hi - I'm very new to dax, coming from SQL/SAS and need to figure out an iterative calculation by two groupings. My source data looks like this:
| Month | Age | Numerator | Denominator |
| Jan-23 | 1 | 10 | 15 |
| Jan-23 | 2 | 12 | 17 |
| Jan-23 | 3 | 14 | 19 |
| Feb-23 | 1 | 20 | 25 |
| Feb-23 | 2 | 21 | 26 |
| Feb-23 | 3 | 23 | 28 |
| Mar-23 | 1 | 8 | 13 |
| Mar-23 | 2 | 9 | 14 |
| Mar-23 | 3 | 10 | 15 |
| Apr-23 | 1 | 15 | 20 |
| Apr-23 | 2 | 16 | 21 |
| Apr-23 | 3 | 17 | 22 |
As a first step I've created a simple measure where numerator is divided by denominator, displayed via a matrix table grouped by Month and Age, result displayed as decimal number below:
What I want now is another table that calculates how much the result number changes at each age iteration. Eg here is what i want to see:
| Month | 1 | 2 | 3 |
| Jan-23 | 0.67 | 0.04 | 0.03 |
| Feb-23 | 0.80 | 0.01 | 0.01 |
| Mar-23 | 0.62 | 0.03 | 0.02 |
| Apr-23 | 0.75 | 0.01 | 0.01 |
Using the numerator and denominator from source data the following is the calculation for what I want to see:
Jan-23, 1 = 10/15 = 0.67
Jan-23, 2 = 12/17 - 10/15 = 0.04
Jan23, 3 = 14/19 - 12/17 = 0.03
Really appreciate any suggestions offered!
Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
OFFSET function (DAX) - DAX | Microsoft Learn
expected result measure: = VAR _v = SUMX ( Data, DIVIDE ( Data[Numerator], Data[Denominator] ) ) VAR _previous = CALCULATE ( SUMX ( Data, DIVIDE ( Data[Numerator], Data[Denominator] ) ), OFFSET ( -1, ALL ( Age[Age] ), ORDERBY ( Age[Age], ASC ) ) ) RETURN _v - _previous
1 Reply
- Jihwan_Kim
Super User
Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
OFFSET function (DAX) - DAX | Microsoft Learn
expected result measure: = VAR _v = SUMX ( Data, DIVIDE ( Data[Numerator], Data[Denominator] ) ) VAR _previous = CALCULATE ( SUMX ( Data, DIVIDE ( Data[Numerator], Data[Denominator] ) ), OFFSET ( -1, ALL ( Age[Age] ), ORDERBY ( Age[Age], ASC ) ) ) RETURN _v - _previous