Max value for a given quarter, if no quarter value exists, use last known value.

[TarrasQ]

Hi,

I am attempting to work out the best method to return the results of the max value of a column, for a given quarter, while if no results are given, return the last known results. (Since the value should always be increasing, Max value should be sufficient)

Something like

= Max(id),allexcept(ID),filter(datemod <= Quarter)

Not sure if the best method would be through a measure, or creating a new table with crossjoin (qtr x ID), and populating the fields that way (still unsure of the dax command for that one as well).

Link to demo data PBIX

DataVersionTable
ID	Date Modified	Value
Test1	01-01-20	1
Test1	01-02-20	2
Test2	01-01-20	1
Test2	05-05-20	2
Test2	01-10-20	4
Test3	01-01-20	2
Test3	10-01-20	3

DateTable
Date	Qtr
…	Qtr1
…	Qtr2
…	Qtr3
…	Qtr4

Expected Results
ID	Quarter	Value
Test1	Qtr1	2
Test1	Qtr2	2
Test1	Qtr3	2
Test1	Qtr4	2
Test2	Qtr1	1
Test2	Qtr2	2
Test2	Qtr3	2
Test2	Qtr4	4
Test3	Qtr2	2
Test3	Qtr3	2
Test3	Qtr4	2
Test3	Qtr1	3

Thanks

[v-kelly-msft · Accepted solution]

Hi @TarrasQ ,

First create a column in table as below:

_Quarter = "Q"&QUARTER('Table'[Date])

Then create a table as below:

Table 2 = CROSSJOIN(VALUES(DataVersionTable[ID]),VALUES('Table'[_Quarter]))

Finally create a measure as below:

Measure 2 = 
var _maxselected=CALCULATE(MAX('DataVersionTable'[Value]),FILTER('DataVersionTable','DataVersionTable'[Quater]<=SELECTEDVALUE('Table'[_Quarter])&&'DataVersionTable'[Quater]<=SELECTEDVALUE('Table 2'[_Quarter])&&'DataVersionTable'[ID]=MAX('DataVersionTable'[ID])))
var _maxnotselected=CALCULATE(MAX('DataVersionTable'[Value]),FILTER('DataVersionTable','DataVersionTable'[Quater]<=SELECTEDVALUE('Table 2'[_Quarter])&&'DataVersionTable'[ID]=MAX('DataVersionTable'[ID])))
Return
IF(SELECTEDVALUE('Table'[_Quarter])=BLANK(),
_maxnotselected,_maxselected)

Remember to create a relationship between Table2 and DataversionTable;

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

This article will help you -https://exceleratorbi.com.au/lastnonblank-explained/

[TarrasQ]

Found one way to do it, which certainly shouldn't be the correct way.

Created an additional table

NewTable = CROSSJOIN(VALUES(DataVersionTable[ID]),values('Calendar'[Quater]))

Then create a column i can use for date lookup

LastdateinQtr = CALCULATE(max('Calendar'[Date]),FILTER('Calendar','Calendar'[Quater] = 'NewTable'[Quarter]))

Then to get the value

LastMaxValue = CALCULATE(MAX(DataVersionTable[Value]),filter(DataVersionTable,DataVersionTable[Date Modified] <= NewTable[LastdateinQtr]),FILTER(DataVersionTable,DataVersionTable[ID] = 'NewTable'[ID]))