cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cathG Frequent Visitor
Frequent Visitor

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

 

 

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)

ALL.pngALLSELECTED.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Floriankx Established Member
Established Member

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

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.

View solution in original post

2 REPLIES 2
Floriankx Established Member
Established Member

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

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.

View solution in original post

cathG Frequent Visitor
Frequent Visitor

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

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. 

 

graph.png

 

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?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 3,410 guests
Please welcome our newest community members: