Year to date measure issue

[Stemar_Aubert]

Hello everybody,

I noticed today the measure I use to calculate the YTD from previous year has an issue.

Date	Sales
Jan 19	4
Feb 19	3
...	...
Jan 20	2

Assuming my table goes from Jan 19 to Feb 20, but I have no sales for the unit I am looking at in Feb, the measure will not take into account the Feb 19, and simply compare Jan 19 to Jan 20.

The measure is the following:

SALES UNITS YTD 19 = 
VAR _MaxMonth = MONTH(MAXX('Actual','Actual'[DATES]))
RETURN
CALCULATE(SUM('Actual'[INDUSTRY UNITS]),DATESYTD(DATEADD('Time'[Dates],-1,YEAR)),MONTH('Time'[Dates])<=_MaxMonth)

This measure is used because it will not create points in the future when put in a line visual (it stops automatically at the max relevant date instead of the current date).

The actual table is linked to the usual calandar table.

How could I tweak this measure so it takes all data from previous year, up to the maximum month available from the current year ? I think the issue here is because of the context. It effectively takes the latest available month, for a specific product, wheras I would need the lastest date of the dataset, so even if there is no sale in Feb 20, it takes Feb 19.

Thanks for any help you may provide.

Added below are two images, one where it works due to having data in Feb 20, and one where it fails to capture Feb 19 data.

Should have 2 as YTD 19 total

[parry2k]

@Stemar_Aubert change your measure as below

SALES UNITS YTD 19 = 
VAR _MaxMonth = MONTH(MAXX(ALL('Actual'),'Actual'[DATES]))
RETURN
CALCULATE(SUM('Actual'[INDUSTRY UNITS]),DATESYTD(DATEADD('Time'[Dates],-1,YEAR)),MONTH('Time'[Dates])<=_MaxMonth)

use ALL to remove the filter context. 

[Stemar_Aubert]

@parry2k Hello and thank you for the modification.

It captures the numbers as you can see below :

This is the intended behavior, however the issue is that now it repeats the value up until now (computer date), wheras it should stop at the maximum dataset date (february 2020). Would you have an idea on how to stop this ? The version without the ALL() has the proper behavior time-wise.

Thanks again !

[parry2k]

@Stemar_Aubert couple of questions:

- what is the last date in your table, is it april, 2020 or feb 2020

- if it is april 2020, why it should stop at feb 2020. what is the logic behind it.

[Stemar_Aubert]

@parry2k 

- The last date in the sales table is Feb 2020. The reason the measure should "stop itself" at the latest date present in the table is to avoid this, and other issues.

Basically this measure is "Prior year YTD", and YTD is defined as the latest data point available in 2020. 

In the model, I have the usual calendar table, with the usual relationship between dimension / fact.

What I don't understand is this : My time is basically a autocalendar and stops in April (Today() formula). I wish to have the measure stop instead of repeating the last calculation, as you can see in the table and the line chart. Thanks a lot.

[parry2k]

@Stemar_Aubert sure there are many ways to control this but just wondering why it is stopping at April 2020, my assumption is that your calendar table has data up to Dec 2020, so why it would stop in April but not Dec. I'm asking because that will help me to get you the right solution

[Stemar_Aubert]

@parry2k 

The sales table stops at 1 Feb 2020.

The date table stops at 27 Apr 2020 (basically it always stops "today").

I've forced the date table to stop at 1 Feb 2020, and it appears to be working, I no longer have the dates repeating.

The issue with this solution, is I have other fact tables, which, depending on when they are updated, might go beyond the sales table.

Ideally I would have each fact table stop itself automatically. 

Before using the ALL() to remove context, the YTD measure would behave as desired time-wise (it stoped in Feb 2020). 

[parry2k]

@Stemar_Aubert no worries, let's fix it without stopping calendar dimension to feb 2020.

Try this but I have to test it

SALES UNITS YTD 19 = 
VAR _MaxMonth = MONTH(MAXX(ALL('Actual'),'Actual'[DATES]))
VAR __stopFlag = IF ( _MaxMonth <= MAX ( Calendar[Date] ), 1 )
RETURN
CALCULATE(SUM('Actual'[INDUSTRY UNITS]),DATESYTD(DATEADD('Time'[Dates],-1,YEAR)),MONTH('Time'[Dates])<=_MaxMonth) *
DIVIDE ( __stopFlag, __stopFlag )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Stemar_Aubert]

@parry2k

I have written it as such :

INDU UNITS YTD 19 = 
VAR _MaxMonth = MONTH(MAXX(ALL('Actual'),'Actual'[DATES]))
VAR _StopFlag = IF ( _MaxMonth <= MAX ( 'Time'[Dates]), 1)
RETURN
CALCULATE(SUM('Actual'[INDUSTRY UNITS]),DATESYTD(DATEADD('Time'[Dates],-1,YEAR)),MONTH('Time'[Dates])<=_MaxMonth * DIVIDE( _StopFlag, _StopFlag ))

However, it doesn't seem to have an effect :

I tried some variations to it (parenthesis mainly) to make sure it apply the _StopFlag to the Month parameter, with no avail. Would you have an idea why ? Thanks !

[amitchandak]

Try one of the following with date calendar

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

YTD QTY forced=
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

YTD QTY forced 2=
var _max = MONTH(today())
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),MONTH('Date'[Date])<=_max)

LYTD QTY forced=
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/