cancel
Showing results for 
Search instead for 
Did you mean: 
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
v-kelly-msft
Community Support
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
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


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Doxa
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/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.

v-kelly-msft
Community Support
Community Support

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!

v-kelly-msft
Community Support
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:

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.

v-kelly-msft
Community Support
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
Did I answer your question? Mark my post as a solution!

View solution in original post

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
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors