Create a new measure that subtracts the latest row of data from the second oldest

[Anonymous]

Hi,

I’ve mapped a Power BI dashboard to a folder where a new excel file is uploaded at the beginning of the month.  I’ve built the Power BI dashboard to combine/merge these files together in to one large table within Power BI.  When a new excel file is added to the folder at the beginning of the month, when I refresh the Power BI dashboard, Power BI reaches out to the folder and brings in the contents of the latest file, ultimately adding it to the existing table in Power BI.

The table name is called ‘Data Table’.  Below is a small example of the data in ‘Data Table’.

Name  Latest_A1C  Acct_Name  Diabetes_IND  Source.Name                  Member ID

Jesse  2/5/2019       Ford             0                        SF Member File 022020     0005

Matt    1/21/2020     BMW            1                       SF Member File 022020     1001

Andy   8/8/2019       Dodge          1                        SF Member File 022020      7000

Jesse  2/10/2020     Ford             1                       SF Member File 032020      0005

Matt    3/3/2020       BMW            1                        SF Member File 032020      1001

Andy   3/1/2020       Dodge          0                        SF Member File 032020      7000

The field [Source.Name] is a created field Power BI builds.  It is added to each row of data automatically.  It is the name of the file where each row of data originated from.  For example (022020 or 032020).  This is convenient in that it contains the MonthYear.  'SF Member File 022020'.  This field allows me to filter/slice the data accordingly and I’d like to somehow use this field to create a measure to subtract the Latest A1C value from the second oldest.

One of the visualizations I’ve created is a matrix table (example below):

I’d like to somehow create a measure that I can add to the ‘Values’ portion of the matrix table that will subtract the Latest A1C value from the second oldest.

I need this measure to subtract the value in the latest [Source.Name] from the second latest value in the [Source.Name] field.  For example, based on the example below I need a measure to be created that I can drag into the matrix visualization and come back with:

-0.1 for ABIGAL MCGRATH 

One thing to note is that the other members would have a value of zero since their values did not change.

The other issue is that some of the data is not always perfect and comes in as a range (0-5%) or even like ADAM BIANCO with a value of (<7%). 

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

you could create a measure to get it:

Measure = 
var _top2=TOPN(2,'Table',[Source Date],DESC) return
CALCULATE(SUM('Table'[Latest_A1C]),FILTER('Table','Table'[Source Date]= MAXX(_top2,[Source Date])))-CALCULATE(SUM('Table'[Latest_A1C]),FILTER('Table','Table'[Source Date]= MINX(_top2,[Source Date])))

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

My mistake.  I just realized that field [Latest_A1C] is not a date.  It's just a  value.  For example:

Name    Latest_A1C    Acct_Name    Diabetes_IND    Source.Name                      Member ID

Jesse     5.1                 Ford               0                        SF Member File 022020      0005

Matt      7.3                 BMW             1                        SF Member File 022020      1001

Andy      6                   Dodge           1                        SF Member File 022020      7000

Jesse      3.2                Ford               1                        SF Member File 032020      0005AA

Matt      7                   BMW             1                        SF Member File 032020      1001

Andy      5.9                Dodge           0                        SF Member File 032020      7000

I've Just now gone ahead and extracted the date from the Source.Name column.  So I now have the MM/DD/YYYY value for each row.  This new column is named [Source Date].  So, for clarification...a correct example of data is:

Name    Latest_A1C    Acct_Name    Diabetes_IND    Source Date                      Member ID

Jesse     5.1                 Ford                0                        02/01/2020                      0005

Matt      7.3                 BMW              1                        02/01/2020                      1001

Andy      6                   Dodge            1                        02/01/2020                      7000

Jesse      3.2                Ford                1                        03/01/2020                      0005

Matt      7                   BMW               1                        03/01/2020                      1001

Andy      5.9                Dodge             0                       03/01/2020                       7000

Jesse      1.1                Ford                1                        04/01/2020                      0005

Matt      7.7                BMW               1                        04/01/2020                      1001

Andy      6.0                Dodge             0                       04/01/2020                       7000

Based on the example above, I'd want the new created measure to return the following for Jesse, Matt and Andy.

-2.1 for Jesse                     (1.1 minus 3.2 = -2.1)

 0.7 for Matt                      (7.7 minus 7    =  0.7)

 0.1 for Andy                     (6.0 minus 5.9  =  0.1)

Sorry for the confusion.

[FrankAT · Accepted solution]

Hi @Anonymous 

with your sample data and my measure you get the following solution:

Measure = 
VAR _Dates = SUMMARIZE('Table','Table'[Source Date])
VAR _LatestDate = MAXX(_Dates,[Source Date])
VAR _PreviousDate = MAXX(FILTER(_Dates,[Source Date] < _LatestDate),[Source Date])
VAR _LatestA1C = CALCULATE(MAX('Table'[Latest_A1C]), 'Table'[Source Date] = _LatestDate)
VAR _PreviousA1C = CALCULATE(MAX('Table'[Latest_A1C]), 'Table'[Source Date] = _PreviousDate)
RETURN
    _LatestA1C - _PreviousA1C

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[amitchandak]

@Anonymous , example with a date table

Second Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

Last Day Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]=max('Date'[Date])),'Date'[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Greg_Deckler]

@Anonymous - This is basically Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

In you case it should be something like:

Measure =
  VAR __Name = MAX([Name])
  VAR __LatestDate = MAX([Latest_A1C])
  VAR __SecondLatestDate = MAXX(FILTER('Data Table',[Latest_A1C] <> __LatestDate),[Latest_A1C])
  VAR __Latest = MAXX(FILTER('Data Table',[Latest_A1C] = __LatestDate),[Diabetes_IND])
  VAR __SecondLatest = MAXX(FILTER('Data Table',[Latest_A1C] = __SecondLatestDate),[Diabetes_IND])
RETURN
  __Latest - __SecondLatest

[Anonymous]

My mistake.  I just realized that field [Latest_A1C] is not a date.  It's just a  value.  For example:

Name    Latest_A1C    Acct_Name    Diabetes_IND    Source.Name                      Member ID

Jesse     5.1                 Ford               0                        SF Member File 022020      0005

Matt      7.3                 BMW             1                        SF Member File 022020      1001

Andy      6                   Dodge           1                        SF Member File 022020      7000

Jesse      3.2                Ford               1                        SF Member File 032020      0005AA

Matt      7                   BMW             1                        SF Member File 032020      1001

Andy      5.9                Dodge           0                        SF Member File 032020      7000

I've Just now gone ahead and extracted the date from the Source.Name column.  So I now have the MM/DD/YYYY value for each row.  This new column is named [Source Date].  So, for clarification...a correct example of data is:

Name    Latest_A1C    Acct_Name    Diabetes_IND    Source Date                      Member ID

Jesse     5.1                 Ford                0                        02/01/2020                      0005

Matt      7.3                 BMW              1                        02/01/2020                      1001

Andy      6                   Dodge            1                        02/01/2020                      7000

Jesse      3.2                Ford                1                        03/01/2020                      0005

Matt      7                   BMW               1                        03/01/2020                      1001

Andy      5.9                Dodge             0                       03/01/2020                       7000

Jesse      1.1                Ford                1                        04/01/2020                      0005

Matt      7.7                BMW               1                        04/01/2020                      1001

Andy      6.0                Dodge             0                       04/01/2020                       7000

Based on the example above, I'd want the new created measure to return the following for Jesse, Matt and Andy.

-2.1 for Jesse                     (1.1 minus 3.2 = -2.1)

 0.7 for Matt                      (7.7 minus 7    =  0.7)

 0.1 for Andy                     (6.0 minus 5.9  =  0.1)

Sorry for the confusion.

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

you could create a measure to get it:

Measure = 
var _top2=TOPN(2,'Table',[Source Date],DESC) return
CALCULATE(SUM('Table'[Latest_A1C]),FILTER('Table','Table'[Source Date]= MAXX(_top2,[Source Date])))-CALCULATE(SUM('Table'[Latest_A1C]),FILTER('Table','Table'[Source Date]= MINX(_top2,[Source Date])))

Result:

and here is sample pbix file, please try it.

Regards,

Lin