topic Re: YTD but value reset each month in the data in DAX Commands and Tips

YTD but value reset each month in the data

Jkh30 — Wed, 14 Jun 2023 03:15:39 GMT

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

Re: YTD but value reset each month in the data

johnt75 — Wed, 14 Jun 2023 08:52:11 GMT

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

Re: YTD but value reset each month in the data

Jkh30 — Thu, 15 Jun 2023 03:45:42 GMT

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

Re: YTD but value reset each month in the data

johnt75 — Thu, 15 Jun 2023 08:35:18 GMT

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.

Re: YTD but value reset each month in the data

Jkh30 — Thu, 15 Jun 2023 09:00:58 GMT

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?

Re: YTD but value reset each month in the data

johnt75 — Thu, 15 Jun 2023 10:46:25 GMT

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

Re: YTD but value reset each month in the data

Jkh30 — Thu, 15 Jun 2023 10:52:56 GMT

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!

Re: YTD but value reset each month in the data

johnt75 — Thu, 15 Jun 2023 11:19:59 GMT

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

Re: YTD but value reset each month in the data

Jkh30 — Mon, 19 Jun 2023 06:58:57 GMT

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?

Re: YTD but value reset each month in the data

johnt75 — Mon, 19 Jun 2023 08:59:26 GMT

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

Re: YTD but value reset each month in the data

Jkh30 — Wed, 21 Jun 2023 07:39:50 GMT

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

Re: YTD but value reset each month in the data

johnt75 — Wed, 21 Jun 2023 11:16:13 GMT

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.