New Measure to find value between two dates

[AW1976NOVA]

Hi,

I’ve mapped a Power BI dashboard to a folder where a new excel file is uploaded at the beginning of every 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 merged table in Power BI is called 'Data Files'.  The table has nearly 70 columns but here is a condensed slimmed down version of the data/table in Power BI:

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

I'd like to create a new measure that will subtract the most recent value in the [Latest_A1C] field based on it's [Source Date] field from the second most recent value in the [Latest_A1C] field based on it's [Source Date] field for each member.

For example, based on the example above, I'd want the new created measure to return the following results 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)

I've posted this question earlier this weekend and another user started to help me.  However, I can't seem to get the solution to work.  Here is what I've tried creating without success:

Measure =

VAR __Name = MAX([Name])

VAR __LatestDate = MAX([Source Date])

VAR __SecondLatestDate = MAXX(FILTER('Data Files',[Source Date] <> __LatestDate),[Source Date])

VAR __Latest = MAXX(FILTER('Data Files',[Latest_A1C] = __LatestDate), [LATEST_A1C])

VAR __SecondLatest = MAXX(FILTER('Data Files',[Latest_A1C] = __SecondLatestDate), [LATEST_A1C])

RETURN

__Latest - __SecondLatest

Can you please assist me in getting the above to work.  It's probably close but I'm stuck.

[amitchandak · Accepted solution]

@AW1976NOVA , try a new column like

column =
var _index = maxx(filter(table, [Name] = earlier([Name]) && [Acct_Name] = earlier([Acct_Name]) && [Source Date] < earlier([Source Date]) ) ,[Source Date])
return
maxx(filter(table, [Name] = earlier([Name]) && [Acct_Name] = earlier([Acct_Name]) && [Source Date] =_index ) ,[ Latest_A1C]) - [Latest_A1C]