cancel
Showing results for
Did you mean:
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
Community Support

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
9 REPLIES 9
Anonymous
Not applicable
Super User

Hi @Doxa ,

Thanks,

Pragati

My Blog: Data Vibe

If this helps, Appreciate a KUDOS!

Proud to be a Super User!

Frequent Visitor

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

 Country/Region Date Population Deceased W.A. Doubling Time Infected Italy 12/03/2020 60,431,283 827 1.9 1,985,253 Italy 13/03/2020 60,431,283 1266 2.1 2,777,841 Italy 14/03/2020 60,431,283 1441 2.2 2,994,083 Italy 15/03/2020 60,431,283 1809 2.2 3,757,544 Italy 16/03/2020 60,431,283 2158 2.5 4,099,267 Italy 17/03/2020 60,431,283 2503 2.6 4,511,024 Italy 18/03/2020 60,431,283 2978 2.8 4,985,303 Italy 19/03/2020 60,431,283 3405 3.0 5,355,590 Italy 20/03/2020 60,431,283 4032 3.1 6,010,133 Italy 21/03/2020 60,431,283 4825 3.3 6,889,756 Italy 22/03/2020 60,431,283 5476 3.4 7,607,952 Italy 23/03/2020 60,431,283 6077 3.5 8,119,577 Italy 24/03/2020 60,431,283 6820 3.7 8,533,561 Italy 25/03/2020 60,431,283 7503 4.0 8,842,008 Italy 26/03/2020 60,431,283 8215 4.2 9,161,069 Italy 27/03/2020 60,431,283 9134 4.4 9,588,656 Italy 28/03/2020 60,431,283 10023 4.7 10,042,461 Italy 29/03/2020 60,431,283 10779 4.8 10,385,752 Italy 30/03/2020 60,431,283 11591 5.1 10,624,358 Italy 31/03/2020 60,431,283 12428 5.4 10,787,259 Italy 01/04/2020 60,431,283 13155 5.6 10,890,371 Italy 02/04/2020 60,431,283 13915 5.9 10,947,247 Italy 03/04/2020 60,431,283 14681 6.3 10,952,014 Italy 04/04/2020 60,431,283 15362 6.6 10,952,014 Italy 05/04/2020 60,431,283 15887 6.8 10,952,014 Italy 06/04/2020 60,431,283 16523 7.2 10,952,014 Italy 07/04/2020 60,431,283 17127 7.7 10,952,014 Italy 08/04/2020 60,431,283 17669 8.0 10,952,014 Italy 09/04/2020 60,431,283 18279 8.4 10,952,014 Italy 10/04/2020 60,431,283 18849 8.8 10,952,014 Italy 11/04/2020 60,431,283 19468 9.2 10,952,014 Italy 12/04/2020 60,431,283 19899 9.5 10,952,014 Italy 13/04/2020 60,431,283 20465 9.9 10,952,014 Italy 14/04/2020 60,431,283 21067 10.4 10,952,014 Italy 15/04/2020 60,431,283 21645 10.8 10,952,014 Italy 16/04/2020 60,431,283 22170 11.1 10,952,014 Italy 17/04/2020 60,431,283 22745 11.5 10,952,014 Italy 18/04/2020 60,431,283 23227 11.9 10,952,014 Italy 19/04/2020 60,431,283 23660 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.

Community Support

Hi  @Doxa,

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

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:

Best Regards,
Kelly

Frequent Visitor

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:

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 Produced Desired Result 1 1 3 3 4 4 2 4 3 4 6 6 7 7 6 7

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

Community Support

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:

Best Regards,
Kelly

Frequent Visitor

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.

Community Support

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
Frequent Visitor

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.

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.