Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
EM_query
Regular Visitor

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:

MonthAgeNumeratorDenominator
Jan-2311015
Jan-2321217
Jan-2331419
Feb-2312025
Feb-2322126
Feb-2332328
Mar-231813
Mar-232914
Mar-2331015
Apr-2311520
Apr-2321621
Apr-2331722

 

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:

EM_query_0-1706161389138.png

 

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:

Month123
Jan-230.670.040.03
Feb-230.800.010.01
Mar-230.620.030.02
Apr-230.750.010.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!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
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.

Jihwan_Kim_1-1706164494281.png

 

 

Jihwan_Kim_0-1706164423000.png

 

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

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
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.

Jihwan_Kim_1-1706164494281.png

 

 

Jihwan_Kim_0-1706164423000.png

 

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

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.