Year to Date Calculations not showing the same values in cards and graph

[JRHans09]

I have a set of cards and a related graph which share the same measures, but for some reason the values are different.

See here:

See here for primary question - why are the values in the cards different than the values in the graph. See the note that the 'vs PY1', 'vs PY2', and 'vs PY3' measures are within the Tooltips. Not sure why this would affect the measures. And, the other values - PY1, PY2, and PY3 are within the Values section and those are not matching, either.

Below are the related measures. The same measures are used for both the Cards and within the Graph:

Here is the table structure showing the only link that currently is filtering the data:

This is the filter description that applies to all of the cards and the graph:

I am guessing this has to do with the date range consisting of the entire year and therfore previous year to date values are calculating for their entire years respectively (within the cards). But, the graph contains all of the measures within the same visual and therfore, as the current year only has  date through 8 April 2020, then the other calculations (PY1, PY2, etc.) will only show through 8 Apr 2019, 2018, etc.?

How do I ensure both the graph and the cards show the same values?

Thank you. Any help would be appreciated.

[Mariusz · Accepted solution]

Hi @JRHans09 

This limits the year to min-max date in the dataset, try this instead.

Dates = 
CALENDAR(
    DATE( YEAR( MIN( Files[DateEntered] ) ), 1, 1 ),
    DATE( YEAR( MAX( Files[DateEntered] ) ), 12, 31 )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz]

Hi @JRHans09 

The difference is that the line chart visual is limited to 8th of April 2020, therefore, PY1 is 8th of April 2019 and so on, but cart visual are not so they will show the values for full years.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[JRHans09]

Additional info - I just copied the graph and converted to a table with the results below:

Why does this happen? It seems there is some 'missing' data that does not appear on the graph, because it is showing data through only 08 Apr 2020, but for previous years, when changing the date range to anything past 08 Apr (in this case, 30 Apr 2020), the total lines on the table show the same results as the cards do, but the graph shows the totals from 08 Apr 2020.

Thanks in advance for the help. I am guessing this is some sort of filter context that I need to understand in more detail.

[Mariusz]

Hi @JRHans09 

What is the last date in your date dimension

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[JRHans09]

@Mariusz - thanks for the quick reply.

The date table is based on the newest date that a file is entered. See here:

Dates = 
CALENDAR(
    MIN( Files[DateEntered] ),
    MAX( Files[DateEntered] )
)

So, the last date should be 08 Apr 2020 (today). 

[JRHans09]

@Mariusz - I am adding another table comparison below. Hope this helps to narrow the search for why this is happening. Any ideas? Do I need to change the calendar to include dates through the end of the year and then change my calculations to hide future date in any previous YTD calculations?

I still find it strange that it is showing totals through the end of April when the measures are YTD measures.

Thanks for your help.

[Mariusz · Accepted solution]

Hi @JRHans09 

This limits the year to min-max date in the dataset, try this instead.

Dates = 
CALENDAR(
    DATE( YEAR( MIN( Files[DateEntered] ) ), 1, 1 ),
    DATE( YEAR( MAX( Files[DateEntered] ) ), 12, 31 )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[JRHans09 · Accepted solution]

@Mariusz - yes, adding the full calendar per year is the solution. I have done this in other reports, but had not applied the logic to this report. Thanks!

Now, I need to adjust my measure to hide any future dates for previous years when viewing a graph with YTD through 'today'.

[Mariusz]

Hi @JRHans09 

You can use a relative date filter on your visuals instead of adjusting Measures

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[JRHans09]

@Mariusz - that works, but the solution from SQLBI also works where future dates without sales in the current year will also hide the related dates in previous years. 

See here from Russo and Ferrari: https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/