Timeline Slicer + YTD Measure

[tutuk28]

Hello, everybody!

I am using the Timeline Slicer in the months and I am having a problem in the current year (2020).

I have filtered the display to only show me the months that have values, that's why it shows me from January to April.

The problem is that when I select from January to April, it compares the whole year of the YTD which is incorrect. I should only consider myself until April:

How could I solve this problem in DAX?

Basically I don't want to be added to the accumulated value of the months of the current year that have not yet passed.

Thank you!

Regards!

[amitchandak]

@tutuk28 , try like

YTD Sales = 
var _max =today()
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=_max)


Last YTD Sales = = 
var _max =date(year(today())-1,month(today()),day(today()))
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"),'Date'[Date]<=_max)

[Greg_Deckler]

What is the calculation you are using? Are you using SAMEPERIODLASTYEAR?

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[tutuk28]

Hi 

I am using the SAMEPERIODYEAR, but I think the problem is the Time Slicer that even if I select from January to April it takes me the whole year.

If I select from January to March the values in the table are correct:

But when I select from January to April it takes me the whole year and that's the problem I have.

Thanks

[amitchandak]

@tutuk28 ,

Not tested. try like

Last YTD Sales = = 
var _max =date(year(today())-1,month(today()),day(today()))
return
CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]),'Date'[Date]<=_max)

[tutuk28]

Hi @amitchandak 

The problem in this way is that if a particular month was selected (example only February) it does not accumulate the value of January, because it does not have the YTD.

Slicer selects me every month from January to December even if I have a filter that shows me only the months that have values.
And in this particular case they ask me to see all the measurements in YTD and they want to see that slicer.

Thanks!

[Greg_Deckler]

I would skip time intelligence and just do it the way that works: Time Intelligence the Hard Way allows you to specifically control how you do your time calculation.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

@tutuk28 , I suggested a couple of solutions. In case it still does not help.Can you share sample data and sample output.

[tutuk28]

Hi @amitchandak  ! Sorry it took me so long to respond.

I'll try to explain the problem better:

When I take measurements with YTD and YTD-1 and the year is complete (2018, 2019) I have no problem.

The problem appears in the current year which is still incomplete (2020).
The problem is particularly in the slider of months, if I select "select all", even if it is in the year 2020 (and has a flag that shows me only the months that have values) it selects me from January to December. That's why it gives me incorrect values (because for YTD-1 it accumulates all the values until December).
In case I don't use the DATESYTD for YTD-1 and only use the SAMEPERIODLASTYEAR as you told me, when I select only one month (example March 2020) it doesn't show me the accumulated of the year.

Thanks for your help !

[amitchandak · Accepted solution]

@tutuk28 ,

Please refer to this file, if this can help. Check the calculation of LYTD, LYD2 vs others