Measure for incremental calculation by multiple conditions

EM_query — Thu, 25 Jan 2024 06:16:51 GMT

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!

Re: Measure for incremental calculation by multiple conditions

Jihwan_Kim — Thu, 25 Jan 2024 06:35:20 GMT

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

topic Measure for incremental calculation by multiple conditions in DAX Commands and Tips

Measure for incremental calculation by multiple conditions

Re: Measure for incremental calculation by multiple conditions