Create new measure that returns latest value in a combined table within a created range

[AW1976NOVA]

Hi,

My Power BI Dashboard is mapped to a data folder on my team’s shared drive.  Every month an excel file is dropped into this folder.  I have built the Power BI Dashboard to combine these files into one table.  Essentially, every month when a new file is dropped to this folder, I manually open the Dashboard and refresh it.  Power BI then reaches out to the folder, pulls in the latest excel file and adds the rows to this existing table within Power BI.

Here is an example of the table with several files combined:

MBR_ID          LATEST_A1C          SOURCE_DATE          NAME          CHL_LVL          OFFICE_AMT         

0101                5.1                          02/01/2020                BILL             9                        $200.00

0200                4.7                          02/01/2020                ANDY          3                        $50.00

0405                6.1                          02/01/2020                SARA           2                        $60.00

0200                4.3                          03/01/2020                ANDY          5                        $25.00

0101                5.1                          03/01/2020                BILL             7                        $350.00

0101                6.7                          04/01/2020                BILL             2                        $225.00

0405                2.3                          04/01/2020                SARA           2                       $35.00

0200                3.7                          04/01/2020                ANDY          5                       $700.00

I wanted to create a range table…so I would be able to create different visualizations to count how many [MBR_ID]’s fell within specified [LATEST_A1C] range, or what the [OFFICE AMT] was within a certain LATEST_A1C] range.  Here is a picture of this table.  It is called ‘MARA Range Table’:

I was able to create a measure that returns the MAX [Latest_A1C] value for different members with this measure:

Max LA1C = CALCULATE(MAX('Data Files'[LATEST_A1C]),FILTER(ALLSELECTED('Data Files'),'Data Files'[MBR_ID]=MAX('Data Files'[MBR_ID])))

I was also able to another measure that works with [Max LA1C] to return the MAX [MARA Score Range] that the [Max_LA1C] value falls within for a given member:

Max MARA Score Range = CALCULATE(MAX('MARA Range Table'[MARA Score Range]),FILTER('MARA Range Table',[Max LA1C]>='MARA Range Table'[MARA Score Min]&&[Max LA1C]<'MARA Range Table'[MARA Score Max]))

I now need to create two new measures:

1) The first new measure needs to return the LATEST [LATEST_A1C] value based on the [Source_Date] field.

2) The second new measure needs to return the MARA Score Range that the LATEST [LATEST_A1C] value falls within.

Here is a picture of the relationships in case you want to see it.

Please let me know if you need more detail or more information.  And thank you in advance!

[v-diye-msft]

Hi @AW1976NOVA 

Not really enough information to go on, please first check if your issue is a common issue listed here:

https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:

1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Greg_Deckler]

@AW1976NOVA There is a lot to unwind here, any chance you can share your PBIX via OneDrive or Box or something?

[AW1976NOVA]

Can you tell me what you mean by PBIX?  I don't think I can share it though because it contains sensitive data.

But I can try to provide more detail if you need it.  I'm closely watching the thread and will respond quickly!

[Greg_Deckler]

@AW1976NOVA PBIX is the file that the Power BI Desktop creates. Maybe if you can create one that contains sample data? I'll try to look more closely if I have time but it takes a lot of effort to mock these up and test out a solution