Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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
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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |