Use CALCULATETABLE to return latest version

[JMG241188]

Hey,

I thought I solved my problem but it is still not summarising the way I want.

In breif, I have a table with a contract idea, a status, and a version number, and I want the table to always show the most recent version, even when I filter out data.

The table below is the raw data;

Contract ID	Status	Version Number
1	BOUND	1
1	PARTIALLY_BOUND	2
1	PARTIALLY_BOUND	3
1	PARTIALLY_BOUND	4
1	PARTIALLY_BOUND	5
1	NEW	6
2	BOUND	1
2	BOUND	2
2	BOUND	3
2	DRAFT	4

This is how I expect it to read, with no filters on, returning the MAX of the version number;

Contract ID	Status	Version Number
1	NEW	6
2	DRAFT	4

If, however, I was to remove "DRAFT" on a filter, I would expect the following table;

Contract ID	Status	Version Number
1	NEW	6
2	BOUND	3

I used the following DAX, and it works but only when filtered to one Contract ID.

Any help would be great appreciated;

IsLatestVersion =

VAR t =

CALCULATETABLE(

ADDCOLUMNS(

    SUMMARIZE(

       Contracts,

       Contracts[Master Contract Version Id]

    ),

    "@id",

    CALCULATE(LASTNONBLANKVALUE(Contracts[Version Number], MAX(Contracts[Version Number]))),

    "@date",

    CALCULATE(LASTNONBLANKVALUE(Contracts[Version Number], MAX(Contracts[Version Number])))

), ALLSELECTED())

RETURN

COUNTROWS(FILTER(t, [@id] = SELECTEDVALUE(Contracts[Version Number])))

[JMG241188]

@amitchandak you are a legend! Thank you so much!

[amitchandak · Accepted solution]

@JMG241188 , refer these measure option

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[JMG241188]

Hey @amitchandak , I have an issue with the formula above, it worked in certain visuals, but not for others.

I ended up with the following formula;

LatestVersion = var _max = maxx(filter(ALLSELECTED(Contracts), Contracts[Master Contract Version Id] = Max(Contracts[Master Contract Version Id])), Contracts[Version Number])

return

CALCULATE(max(Contracts[Version Number]), filter((Contracts) ,  Contracts[Version Number] = _max))

This is OK to use as a Is Not Blank filter on tables where each contract is separated, but it doesn't work when the data is aggregated.

What I need to do is return the $ limit of each of the latest versions, summed up together. I tried to change what the formula was returning, and again, it works at line level, but when aggregating contracts together, it only returns one value. Which is likely expected with the DAX measure, but I need to know how to take all of these limits, and sum them together.

LatestVersionLimit = var _max = maxx(filter(ALLSELECTED(Contracts), Contracts[Master Contract Version Id] = Max(Contracts[Master Contract Version Id])), Contracts[Version Number])

return

CALCULATE(SUM('Contract Sections'[Overall Limit]), filter((Contracts) ,  Contracts[Version Number] = _max))

Thanks so much,

J