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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Doxa
Frequent Visitor

Measure where result of calculation in t depends on the result of the same calculation in t-1

Hi

 

I am attempting to recreate an Excel document in which the result in time t depends on whether the calculation in t is greater than in t-1 but I'm not having much luck.  If the calculation in t > calculation in t-1 then it should use t and if not should carry forward t-1 (the result illustrated below).

 

        26,930        26,930        54,983        54,983        54,983        55,592        57,097

 

For context, the figures above are are calculated numbers of people who have been infected, and thus should not go lower.

 

I would simply import the results of the calculation into Power BI from Excel, but I need a couple of the parameters of the calculation to be dynamic, which I have achieved through using drop down filters and completing the calculation via a measure.

 

I hope this, or some other work around, is possible.  Any help towards figuring this out would be greatly appreciated!  Please let me know if any clarification is required.

 

1 ACCEPTED SOLUTION

Hi @Doxa ,

 

Just modify the calculated column to a measure,see below:

 

previous one = 
VAR a=CALCULATE(MAX('Table'[Result Currently Produced]),FILTER('Table','Table'[Index]=MAX('Table'[Index])-1))
Return
IF(MAX('Table'[Result Currently Produced])>a,MAX('Table'[Result Currently Produced]),a)

 

After that,combine the measure of parameter to make it dynamic.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

9 REPLIES 9
Anonymous
Not applicable
Pragati11
Super User
Super User

Hi @Doxa ,

 

The content is not enough to answer your query. Can you pleasea add more details like smaple data, etc.?

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Of course.  Here is a sample of the data from which the calculation is made for those who have been infected over time:

 

Country/RegionDatePopulationDeceasedW.A. Doubling TimeInfected
Italy12/03/2020         60,431,283827                                   1.9           1,985,253
Italy13/03/2020         60,431,2831266                                   2.1           2,777,841
Italy14/03/2020         60,431,2831441                                   2.2           2,994,083
Italy15/03/2020         60,431,2831809                                   2.2           3,757,544
Italy16/03/2020         60,431,2832158                                   2.5           4,099,267
Italy17/03/2020         60,431,2832503                                   2.6           4,511,024
Italy18/03/2020         60,431,2832978                                   2.8           4,985,303
Italy19/03/2020         60,431,2833405                                   3.0           5,355,590
Italy20/03/2020         60,431,2834032                                   3.1           6,010,133
Italy21/03/2020         60,431,2834825                                   3.3           6,889,756
Italy22/03/2020         60,431,2835476                                   3.4           7,607,952
Italy23/03/2020         60,431,2836077                                   3.5           8,119,577
Italy24/03/2020         60,431,2836820                                   3.7           8,533,561
Italy25/03/2020         60,431,2837503                                   4.0           8,842,008
Italy26/03/2020         60,431,2838215                                   4.2           9,161,069
Italy27/03/2020         60,431,2839134                                   4.4           9,588,656
Italy28/03/2020         60,431,28310023                                   4.7         10,042,461
Italy29/03/2020         60,431,28310779                                   4.8         10,385,752
Italy30/03/2020         60,431,28311591                                   5.1         10,624,358
Italy31/03/2020         60,431,28312428                                   5.4         10,787,259
Italy01/04/2020         60,431,28313155                                   5.6         10,890,371
Italy02/04/2020         60,431,28313915                                   5.9         10,947,247
Italy03/04/2020         60,431,28314681                                   6.3         10,952,014
Italy04/04/2020         60,431,28315362                                   6.6         10,952,014
Italy05/04/2020         60,431,28315887                                   6.8         10,952,014
Italy06/04/2020         60,431,28316523                                   7.2         10,952,014
Italy07/04/2020         60,431,28317127                                   7.7         10,952,014
Italy08/04/2020         60,431,28317669                                   8.0         10,952,014
Italy09/04/2020         60,431,28318279                                   8.4         10,952,014
Italy10/04/2020         60,431,28318849                                   8.8         10,952,014
Italy11/04/2020         60,431,28319468                                   9.2         10,952,014
Italy12/04/2020         60,431,28319899                                   9.5         10,952,014
Italy13/04/2020         60,431,28320465                                   9.9         10,952,014
Italy14/04/2020         60,431,28321067                                 10.4         10,952,014
Italy15/04/2020         60,431,28321645                                 10.8         10,952,014
Italy16/04/2020         60,431,28322170                                 11.1         10,952,014
Italy17/04/2020         60,431,28322745                                 11.5         10,952,014
Italy18/04/2020         60,431,28323227                                 11.9         10,952,014
Italy19/04/2020         60,431,28323660                                 12.3         10,952,014

 

The calculation also requires two other parameters: 

- Time to Death: Set to 14, although variable in Power BI

- IFR:  Set to 0.6%, also variable in Power BI

 

The Excel formula is as follows (replacing cell references with names): 

 

 Infected = IF(IFERROR((2 * "Time to Death"/"W.A. Doubling Time") * ("Deceased" / "IFR"),) > Infected Calculation Previously, IFERROR((2 * "Time to Death"/"W.A. Doubling Time") * ("Deceased" / "IFR"),), Infected Calculation Previously)

 

E.g. if this formula was in F2, it would refer to F1 if F2 did not produce a larger value.

Hi  @Doxa,

 

I made a sample table to show you how to get the value of "t-1",see below:

Annotation 2020-04-23 175359.png

1.First go to query editor>add column >Index column;

2,Then create a measure as below:

 

Measure = 
CALCULATE(MAX('Table'[Value]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1))

 

Or a calculated column as below:

 

Column = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))

 

Finally you will see:

Annotation 2020-04-23 175550.png

For the related sample .pbix file ,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

Hi @v-kelly-msft

 

Thank you very much for your response.

 

I tried something similar myself to try and fix the issue, but by creating index columns and merging the data with itself in the query editor so i had data for time t and t-1 alongside each other.  I then created two measures: one to complete a calculation using time t data, and another that used t-1 data:

 

 

Current Infected = ((2 * MAX('Time to Death Selection'[Time to Death])/MAX(CountryRegion[W.A. Doubling Time])) * (MAX(CountryRegion[Modified Deceased])/MAX('IFR Selection'[IFR])))

Prev Infected = ((2 * MAX('Time to Death Selection'[Time to Death])/MAX(CountryRegion[W.A. Doubling Time Prev])) * (MAX(CountryRegion[Modified Deceased Prev])/MAX('IFR Selection'[IFR])))

 

 

In the measures above, "Time to Death" and "IFR Selection" are the same values in both equations.

 

The result is the following table:

 

Infected Table.PNG

I then created a measure to compare the current and previous calculation to find the largest, but this doesn't compare the current infected to the largest infected number calculated so far, which has resulted in the infected number falling, rather than staying the same unless the next calculation produces a higher result.

 

For example, say the results below are the outcomes of a measure:

 

Result Currently ProducedDesired Result
11
33
44
24
34
66
77
67

I hope this provides some clarity and thanks again for the assistance - very much appreciated!

Hi @Doxa ,

 

First get the value of t-1 row,using below dax expression;

 

Column = 
VAR a=CALCULATE(MAX('Table'[Result Currently Produced]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))
Return
IF('Table'[Result Currently Produced]>a,'Table'[Result Currently Produced],a)

 

Then create a measure as below:

 

Measure = 
var a=CALCULATE(MAX('Table'[Result Currently Produced]),FILTER(ALL('Table'),'Table'[Index]=SELECTEDVALUE('Table'[Index])-1))
var b=CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),'Table'[Index]=SELECTEDVALUE('Table'[Index])-1))
Return
IF(MAX('Table'[Result Currently Produced])>a&&MAX('Table'[Result Currently Produced])>b,MAX('Table'[Result Currently Produced]),b)

 

Finally you will see:

Annotation 2020-04-27 172747.png

 

For the related .pbix file ,pls click here.

 
 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

Hi @v-kelly-msft 

 

Thanks very much for the help - looking really good!

 

I'm not sure that working from a column will work in the context of what I am trying to achieve as the formula has parameters that are adjustable by filters.  Your final result for the measure is what I am after, in that it doesn't change unless the result is bigger, however, it is my understanding that columns are not dynamic and thus the calculation cannot be adjusted for changes to "Time to Death" and "IFR" as per my measure equations to calculate the number infected:

 

Infected = ((2 * MAX('Time to Death Selection'[Time to Death])/MAX(CountryRegion[W.A. Doubling Time])) * (MAX(CountryRegion[Modified Deceased])/MAX('IFR Selection'[IFR])))

 

I am aiming for the equation to recalculate if, for example "IFR" is changed from, say 0.6% to 0.4% or "Time to Death" changed from 14 to 12.

 

I hope that makes sense - really appreciate the assistance.

Hi @Doxa ,

 

Just modify the calculated column to a measure,see below:

 

previous one = 
VAR a=CALCULATE(MAX('Table'[Result Currently Produced]),FILTER('Table','Table'[Index]=MAX('Table'[Index])-1))
Return
IF(MAX('Table'[Result Currently Produced])>a,MAX('Table'[Result Currently Produced]),a)

 

After that,combine the measure of parameter to make it dynamic.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

Hi Kelly

 

Apologies for the delayed response.  Thanks for your help so far, much appreciated.

 

"Result Currently Produced" is actually the result of a dynamic measure rather than a column, as described in a previous post (formulas below) so will this work for that situation?

 

Infected = ((2 * MAX('Time to Death Selection'[Time to Death])/MAX(CountryRegion[W.A. Doubling Time])) * (MAX(CountryRegion[Modified Deceased])/MAX('IFR Selection'[IFR])))

 

In the above formula, Time to Death, W.A. Doubling Time, Modified Deceased and IFR are columns imported from Excel, with IFR and Time to Death variable via filter.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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