YTD but value reset each month in the data

[Jkh30]

Hello everyone,

I have a requirement that shows the visuals from January-December even though the data only has January-February.
If the data updated with March, the visual carry on with March value through the rest of the months.
For example:

Date	Value	Item
01-01-23	10	A
31-01-23	20	A
01-02-23	30	A
25-02-23	10	A

I need to show the average 15 value of January, 20 of February, 20 of March, 20 of April and so on. I was thinking using YTD function and succeed to show the value for March, April and so on. The issue that I encounter is using TOTALYTD function will carry on the value of January unto February, March, April and so on.

Example of expected value:

Month	Item	Average Value
January	A	15
February	A	20
March	A	20
April	A	20
May	A	20
June	A	20

And for the rest of the months in the year

May anyone help with this? Thank you

[johnt75]

Try

Average value =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR MaxDateWithValues =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR MaxDateToUse =
    MIN ( MaxDate, MaxDateWithValues )
VAR DatesToUse =
    CALCULATETABLE (
        DATESMTD ( 'Date'[Date] ),
        TREATAS ( { MaxDateToUse }, 'Date'[Date] )
    )
VAR Result =
    CALCULATE ( AVERAGE ( 'Table'[Value] ), DatesToUse )
RETURN
    Result

[Jkh30]

Hi @johnt75, it works like a charm! Thank you. However, I have a slight change of the requirement, may you help me with this?

The requirements is like this:

The table contain these rows, on January the value should show 15, February 20, March 45, April 50, May 50, and 50 for the rest of the months. The requirement is to show the value of the latest date of the records for the rest of the dates.

Date	Value	Item
01-01-23	10	A
31-01-23	20	A
01-02-23	30	A
25-02-23	10	A
15-03-23	40	A
16-03-23	50	A

Expected result:

Month	Item	Average Value
January	A	15
February	A	20
March	A	45
April	A	50
May	A	50
June	A	50

And so on for the rest of the dates/months.

Many thanks

[johnt75]

You can use

Latest Value = VAR MaxDate = MAX( 'Date'[Date] )
VAR Result =
	SELECTCOLUMNS(
		INDEX(
			1,
			CALCULATETABLE( 'Table', 'Date'[Date] <= MaxDate ),
			ORDERBY( 'Table'[Date], DESC ),
			,
			PARTITIONBY( 'Table'[Item] ),
			MATCHBY( 'Table'[Date], 'Table'[Item] )
		),
		"@val", 'Table'[Value]
	)
RETURN
	Result

You might get a red line under the 'Table'[Value] but thats just Intellisense playing up, it will work.

[Jkh30]

Thank you very much for your quick response! However, it's not fulfill the requirements since it only showing the latest value in each month and not the average value. Plus, the visual now only showing the month that only exist in the data (is not showing the rest of the months based on the latest data)

Or maybe I just missing some steps?

[johnt75]

Just reread the thread and I'm not clear how your new requirement differs from the original.

[Jkh30]

The original requirements was to shows the visual for the rest of the months with the latest month average. In this case the available latest average is on February which is 20 that should appear for the rest of the month after February (March, April, May and so on).

The new requirements needs to show for March and so on with 10 as value since the last record is on February and it has 10 as a value based on this table.

Date	Value	Item
01-01-23	10	A
31-01-23	20	A
01-02-23	30	A
25-02-23	10	A

So the expected result from the new requirement is:

January: 15 (average on January)

February: 20 (average on February)

March: 10 (the latest record which is 25-Feb-23)

April: 10 (the latest record which is 25-Feb-23)

May: 10 (the latest record which is 25-Feb-23)

and so on for the rest of the months.

Thanks for your help!

[johnt75]

OK, try

Average value =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR MaxDateWithValues =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR MaxDateToUse =
    MIN ( MaxDate, MaxDateWithValues )
VAR DatesToUse =
    CALCULATETABLE (
        DATESMTD ( 'Date'[Date] ),
        TREATAS ( { MaxDateToUse }, 'Date'[Date] )
    )
VAR Result =
    IF (
        EOMONTH ( MaxDateToUse, 0 ) = MaxDate,
        CALCULATE ( AVERAGE ( 'Table'[Value] ), DatesToUse ),
        CALCULATE ( MAX ( 'Table'[Value] ), TREATAS ( { MaxDateToUse }, 'Date'[Date] ) )
    )
RETURN
    Result

[Jkh30]

Hi @johnt75, thanks for your help. I really appreciate it. However, there is still some issues occuring. When the data has the row of different year, the formula doesn't works as expected.

For example:

If the data only has 2023 data, the formula works as expected

Date	Value	Item
01-01-23	10	A
31-01-23	20	A
01-02-23	30	A
25-02-23	10	A
15-03-23	40	A
16-03-23	50	A

This is correct:

But when I added 2024 data, even though I already filtered the report using slicer for 2023, the result is like this:

Date	Value	Item
01-01-23	10	A
31-01-23	20	A
01-02-23	30	A
25-02-23	10	A
15-03-23	40	A
16-03-23	50	A
01-01-24	1000	A

It's not showing the remainder months of the 2023's year. When it's filtered to 2024, it works:

May you please to help me with this?

[johnt75]

It needs a tweak to make sure that the last date with values is on or before the current max date from the visual

Average value = 
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR MaxDateWithValues =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS (), 'Date'[Date] <= MaxDate )
VAR MaxDateToUse =
    MIN ( MaxDate, MaxDateWithValues )
VAR DatesToUse =
    CALCULATETABLE (
        DATESMTD ( 'Date'[Date] ),
        TREATAS ( { MaxDateToUse }, 'Date'[Date] )
    )
VAR Result =
    IF (
        EOMONTH ( MaxDateToUse, 0 ) = MaxDate,
        CALCULATE ( AVERAGE ( 'Table'[Value] ), DatesToUse ),
        CALCULATE ( MAX ( 'Table'[Value] ), TREATAS ( { MaxDateToUse }, 'Date'[Date] ) )
    )
RETURN
    Result

[Jkh30]

Hi @johnt75 , I really appreciate that you always helping me with this. I tried to put the dax into my data, but it doesn't work as expected, here is my data and the result:

This is my data for one particular item from my fact table.

Here is how it's appear using the formula and the date from calendar or dimension date:

It stopped at 31-May-2023 and not going through the rest of the months of the year. I tried to tweak the dax you provided but unfortunately I can't find the solution yet. Also, here the line chart showing:

It's not showing the value for June-December on 2023.

May you please help me with this? Thank you very much

[johnt75 · Accepted solution]

I've created a model with the data you've given, with the date table linked to the fact table on the activation date column and it works for me.

I would use Performance Analyzer to copy the query used for the chart visual and then examine it in DAX Studio. That should show you if any additional filters are being applied.