DAX Logic to repeat value on MAX Date up to the end of 2020 based on a conditional column

[thiagofcs]

Hello, I need help doing the logic in DAX as below

Sample Data:

Date	Ticket	KPI	Type	Value
01/01/2020	TICK01	KPI01	Recurring	10
01/02/2020	TICK01	KPI01	Recurring	8
01/01/2020	TICK01	KPI02	No Recurring	7
01/02/2020	TICK01	KPI02	No Recurring	6
01/01/2020	TICK02	KPI03	Recurring	12
01/02/2020	TICK02	KPI03	Recurring	13

The DAX Measure should do the logic:

When Type = No Recurring:
-> do nothing, just return the Value column on their respective row.

When Type = Recurring:
-> Should get the value on the MAX date for each Ticket/KPI (one ticket can have one or more KPI`s either recurring or no recurring) and repeat this value until the last month of 2020 (01/12/2020)

So, for the example above it have to repeat the value on MAX date of TICK01/KPI01 (8) and TICK02/KPI03 (13) up to Dec/2020.

TICK01/KPI02 will have it value as is in the sample table (just for january and february 2020)

Thanks in advance,

Thiago

[camargos88]

@thiagofcs ,

You asked for a conditional column in the title. That's is possible to do with a measure as well.

You can add a filter to the formula till 12/2020 or give us some example of what you need with data (input and output).

[thiagofcs]

Hi camargos88.

It`s almost this. But the past months will not get the value on max date. And it should repeat the value on max date only for the future months up to 12/2020. 
I think this should be done in a measure not in a column (maybe some preparation in the column it`s fine) but the measure because we have to join this table with a calendar table that goes up to 12/2021.

[camargos88]

@thiagofcs ,

Try this code for a new column:

Column = 
IF('Table'[Type] = "No Recurring", 'Table'[Value],
VAR _maxDate = CALCULATE(MAX([Date]), ALLEXCEPT('Table', 'Table'[Ticket], 'Table'[KPI]))
RETURN CALCULATE(SUM('Table'[Value]), FILTER(ALLEXCEPT('Table', 'Table'[Ticket], 'Table'[KPI]), [Date] = _maxDate)))

[thiagofcs]

Hi,

Any tip for this one?