Measure that calculates the latest value every time source is updated

[7ea8ea]

Hi all,

I have a data set that looks something like this:

Group	Value	Date
A	8	1/12/20
B	9	1/12/20
A	6	1/1/20
B	8	1/1/20

I would like to create two measures that give the latest value as the output for each group. For example, in this case, I would like the measure to output 6 for group A, and 8 for group B. I am working with live data. Therefore, data will be updated every month to include the month's group and value. For example, when it is February, 2 new rows will appear with their own unique values. I would like to just pull out the latest values only. Any help would be appreciated. Thanks

[v-alq-msft · Accepted solution]

Hi, @7ea8ea 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create two measures as below.

Result A = 
var maxdate = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALL('Table'),
        [Group]="A"
    )
)
return
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        [Group]="A"&&
        [Date]=maxdate
    )
)

Result B = 
var maxdate = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALL('Table'),
        [Group]="B"
    )
)
return
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        [Group]="B"&&
        [Date]=maxdate
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@7ea8ea , I think the date should be 1/1/21 . Otherwise 1/12/20 is max (in mm/dd/yyyy) format. I have that.

The attached file should work with the correct date or date format. Please find the file after the signature.

In case you need two measures

calculate(max(Table[Value]), filter(Table, Table[Date] = calculate(Min(Table[Date]), allexcept(Table, Table[Group]))) , Table[group] = "A")

calculate(max(Table[Value]), filter(Table, Table[Date] = calculate(Min(Table[Date]), allexcept(Table, Table[Group]))) , Table[group] = "B")