grand total with ALLSELECTED does not show previous year total in a filter context

[cathG]

I am working with data that I am aggregating at a daily level. I have few years worth and I am interested in looking at the growth year on year, on a daily and monthly level. My dataset have up to 8 years worth of data (HDSdata).

My dates are in a reference table (Dates) which also contains a field that indicates the month and if there is a special holiday (for example Easter, Bank Holidays, etc).

In order to find the previous year volume, I am using a combination of calculated field and measure. Because my volume is affected by a weekly seasonality, I am aligning the data by subtracting 364 days instead of using SAMEPERIODLASTYEAR function. Indeed, 31st of March 2018 was a Saturday and I want to compare it with the same Saturday last year, hence 1st of April.

Here are my work so far:

1. I have a measure that calculate the total volume: 

   Total Vol(HDS) = CALCULATE(SUM(HDSdata[total_HDS]))

2. In my Dates Reference table, I have added a calculated field that find the previous year day aligned. 

   LastYearDate = DATEADD(Dates[date], -364, DAY)

3. I have created a measure that indicates the first day of the previous year 

   Last year = DATE(YEAR(TODAY())-1,01,01)

4. I am calculating my previous year volume using this measure: 

   Total Vol(HDS) 2017 = 
   CALCULATE([Total Vol(HDS)],
   FILTER(ALL(Dates), Dates[date] >= [Last year] && Dates[date] >= MIN(Dates[LastYearDate]) && Dates[date]<= MAX(Dates[LastYearDate])))

The measure works well to give me the appropriate volume from previous year day aligned. However, when I put the data into a table and apply a filter (for example month and holiday) the Grand Total does not match. It is summing all the records in the original table. I have then changed the ALL function to ALLSELECTED which does show a decent monthly total but does not show me the last day of the selected month.

So when I seelct March on my sclicer the ALL function shows me every dates in March but the monthly total shows the year total insteas. And when I use the ALLSELECTED function, it shows me still the correct day alignerd total but not for the 31st of March since the day aligned was April 1st.

Here with the ALL (first image) and ALLSELECTED (second image)

Is there a way to keep both of two worlds: having all my daily total aligned for the selected month and having the monthly total right?

Thank you,

Cath

[Floriankx · Accepted solution]

Hello,

great explanation, all necessary information give, that's really great.

Here the Calculate is obsolete. Total Vol(HDS) = CALCULATE(SUM(HDSdata[total_HDS]))

Try to Calculate your Last year Value:= Total Vol(HDS)_LY:=CALCULATE([Total Vol(HDL)],DATEADD(Dates[date], -364, DAY)).

This should give you the same result with less trouble. So you don't need your calculated column, because it is tricky to handle to date columns. I avoid it all the time.

I don't think you have to use ALL neither ALLSELECTED in this case.

[cathG]

Hi Floriankx,

Thank you for the quick answer. I have tried your suggestion and it is indeed simpler! However when I am using the measure in my visual it is showing all the previous year data. If I select a month, the visual looks lie the one below. And for some reasons it does not show on the tables. 

I could make it work by adding either a page or visual filter to keep the current year dates. Then it shows the 2017 line correctly with and without the filter.

Is there a way to correct this into the measure itself or is the page/visual filter the only solution?