cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alaxus
New Member

Year to date vs previous year to date line chart with slicer filter

Good morning to all of you, 

 

I would like to see a line chart where I can compare the year to date (YTD) vs the previous year to date (PYTD) on my net profit. 

 

The YTD measure looks like this: 

Act YTD = TOTALYTD(Facts_Sheet[# Ttl Act USD], 'Date'[FullDateAlternateKey].[Date])
 
The PYTD measure looks like this: 
# Act PYTD = TOTALYTD(Facts_Sheet[# Ttl Act USD], DATEADD('Date'[FullDateAlternateKey].[Date], -12,MONTH))
 
I have added to my dashboard a settings menu in which you have different filter options. As example you can filter via a slicer: the month, the year and the company. I need this filter because of other charts within my dashboard. So far so good. 
 
Now comes my problem: The YTD vs PYTD line chart would be filtered by the above three options, which means that the line chart would reflect the relevant month which is chosen only. In this example the filter would be April.
 
Alaxus_0-1652859812309.png

 

 But I want to compare the YTD vs PYTD for the full year on a month to month basis. So I have excluded the YTD vs PYTD line chart visial from the month slicer filter. 
The result is of course a full year view. 
 
Alaxus_1-1652859869352.png

But as you propably will have notice, the measure for the actual year does not stop by April, but its calculating the whole year which looks strange. 

 

So what I would like to see is a line chart where the YTD line does end with the relevant month slicer setting, but the PYTD line reflects the whole year. Here my example: 

Alaxus_2-1652860194578.png

 

I believe I need to adjust my measures, but I can not find a solution online. Can one of you help?

 

Your reply is much appreciated and I thank you in advanced. 

 

Brgds

Alaxus

 

 

 

 
4 REPLIES 4
JamesFr06
Super User
Super User

ops sorry it's DATESYTD

Dear JamesFr06, 

 

thanks for your correction. 

 

I played around with your two measure suggestions but they do not match with my idea to stop calculating at the relevant month which is used in the slicer. I will try to explain with the options: 

 

1) Following are the two measures: 

Act YTD = CALCULATE(Facts_Sheet[# Ttl Act USD], DATESYTD('Date'[FullDateAlternateKey]))
 
Act PYTD = CALCULATE(Facts_Sheet[Act YTD], SAMEPERIODLASTYEAR('Date'[FullDateAlternateKey].[date]))
 
result is this: 
Alaxus_0-1652966439075.png

as you will see, the line Act YTD does not stop at the month April, but insready also uses the date which is already in the system for May. FYI, the May data is not yet finalized, i.e. for a reader it is misleading since additional bookings could increase or deacrease the net profit as relfected in the line chart. 

 

2)

Act YTD = CALCULATE(Facts_Sheet[# Ttl Act USD], DATESYTD('Date'[FullDateAlternateKey].[date]))
 
Act PYTD = CALCULATE(Facts_Sheet[Act YTD], SAMEPERIODLASTYEAR('Date'[FullDateAlternateKey].[date]))
 

Result is this: 

 

Alaxus_1-1652966767855.png

 

 

so the difference is: 

1) it does not add the monthly result with the next month but instead reflects each individual month result. 

2) it does calculate and adds the monthly result with the previous month result. 

 

If I try to use.. = calculate(sum( ... it does not work at all. 

 

for sake of good order here is the Visualization setting: 

 

Alaxus_2-1652967568117.png

 

 

Do you have another solution? If not somebody else?

 

In any case, many thanks for your help so far 🙂 

Brgds

Alaxus

 

JamesFr06
Super User
Super User

Hi Alaxus,

 

Try these measures :

Act YTD = calculate(sum(Facts_Sheet[# Ttl Act USD]), DATEYTS('Date'[FullDateAlternateKey]))
# Act PYTD=calculate([Act YTD],SAMEPERIODLASTYEAR('Date'[FullDateAlternateKey]))
 

Thank you James Fr06, 

 

unfortunately I get the following advise: 

Alaxus_0-1652865677799.png

and also DATEYTS can not be found. 

 

FYI, [#Ttl Act USD] is a measure. Is that from importance?

 

Many thanks in advanced. 

 

Brgds

Kai 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors