DAX for earliest value

[rgalvez]

Hello experts!
My dataset is built of three tables: Users, Mastery and Exams.

The Users table is the link (one-to-many relation) between Mastery and Exams tables, as follows:

The expected outcome is a new table that includes all the fields from the Exams table and a new column with the Mastery value for the initial date [Initial Date Mastery], for that user, for that moduleID (taken from the Mastery[Initial Date Mastery]), and another column with the Mastery value for the final date [Final Date Mastery], for that user and for that moduleID (taken from the Mastery[Final Date Mastery]).

This is how the outcome should look:

If you follow the colors, the green fields are from the Exams table, and the orange values are from the Mastery table. 

Up to this point, this is totally doable.

Now, here's the difficult part:  There are empty dates the Mastery table. In our outcome table, if the [Initial Date Mastery] value is empty, look down for the earliest [Initial Date] that has a [Initial Date Mastery] value.

Same request for [Final Date Mastery]:  If the [Final Date Mastery] value is empty, look down for the earliest [Initial Date] with a [Final Date Mastery] and populate the [Final Date Mastery] field.

Here's an example:

User 111HT, in moduleID 473.  For [Initial Date] 7/30/2019 there's no Mastery value. The earliest Mastery value for that user, in that module is 3.5 on [Initial Date] 6/28/2019.  Thus, 3.5 should be populated in the row for 7/30/2019, as follows:

I hope this makes sense. I'm attaching an Excel sheet with sample data from the three tables.

Thank you!

[arb1962]

Hi Amit,

Here ia a scenario

Column 1 = date (take any random  years)

Column 2 to 4 = F1 F2 F3 ....F10 (platfroms) corresponding tweleve dates would have random 0 or 1 as an input for F1, F2 , F3 (represents 1 = issues, 0 = no issues) 

Column 5 - 8 are observations with heading Item 1 to Item 4. 

Item1 - Good, bad and Ok

Item2 - Low, Medium, High

Item3 - TK1, TK2, TK3

Item4 - BD1, BD2, BD3

All input for Item1 to Item4 random

I am looking for results based on Items versus Platfrom based on dates (yearly, quarterly, montly, weeky and even daily)

I tried to solve by unpivoting the platfroms and went for a solution but I am not getting the result. 

Could you help me out 

[amitchandak · Accepted solution]

@rgalvez , a new column based on what I got

diff =

var _max = maxx(filter(Table, [Rep Id] = earlier([Rep id])  && [Intial Date] < earlier([Intial date]) ), [Intial  Date])

return

 maxx(filter(Table, [Rep Id] = earlier([Rep id])  && [Intial Date] =_max ), [Score]) - [Score]

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

[rgalvez]

Thanks! Not exactly what I was asking for, but the link to the Earlier approach was very useful.