YTD Calulation Not working with slicers of visual filters

[zenton]

Hi,

I have a calculation for YTD which works perfectly. It is based on my Count Odour measure.

I get a result of 15 which is correct. The Count of odour for all data is 49.

The issue I am getting is when I add a visual filter or user a slicer to filter by Hub the value for Hub 1 should be 0 but the value I get is 3 which is the Total of all data not YTD.

YTD Odour = CALCULATE('Complaints'[Count Odour], DATESYTD( Complaints[Received Date], "30/6"))

Count Odour = CALCULATE(COUNT(Complaints[Complaint type]),FILTER('Complaints','Complaints'[Complaint type]="Odour"))+0

[Anonymous]

Just to clarify, are you wanting your YTD Odour to run and ignore filters?  Have you tried making use of functions such as ALL?

An example might be 

YTD Odour = CALCULATE('Complaints'[Count Odour], ALL('Complaints'), DATESYTD( Complaints[Received Date], "30/6"))

[zenton]

Hi,

Thank you for your quick reply. No, the all Filters is not what I need it un-filters what I previously filtered in the Count Odour measure. I don’t want to ignore filters I want to have TYD and Filter by Comnplaint type. The First table below shows the correct total YTD as 15 but the amounts in the table add up to 21. The second table shows the correct data YTD

Thanks Rodney

[zenton]

As an update I tried adding the filter into the formula and it worked perfectly with a result of 0, see formula below.

Odour YTD ST0001 = CALCULATE('Complaints'[Count Odour], FILTER('Complaints',[Facility Code]="ST0001"), DATESYTD( Complaints[Received Date], "30/6"))

If I use the formula below with no filter and then add the Facility Code as a Visual Filter in the Visualisation I get the wrong result of 3.

Odour YTD = CALCULATE('Complaints'[Count Odour], DATESYTD( Complaints[Received Date], "30/6"))

I have about 300 of these visualisations and I can use a custom formula for each one. I want to duplicate the visualisation and then add Visual filters to separate the data

[Anonymous]

Coming back to my last comment. Have a look into the Funciton ALLEXCEPT.  There are a few different ALL type functions, you should be able to find one that you might need.

[zenton]

I finaly worked out why I was not getting the expected results.

Each facilty has a different set of dates ranges depending when the data entry was done.

The Total FYTD for all Facilities is correct because it looks at the entire date range for all the data

If a facility has no dates for this Fiscal year the result is the Total for last years FTYD.

If the facility has dates for this Fiscal year the result is the Total for This years FTYD.

I want the results to equal zero if there are no dates for this Fiscal Year not calculate last years FYTD

[zenton · Accepted solution]

For anyone else that has had a similar issue I fixed my issue by addin a IF() statement checking for dates with data in this financial year.

YTD Odour = IF(MAX(Complaints[Received Date])<[End FYTD],0,CALCULATE('Complaints'[Count Odour], DATESYTD( Complaints[Received Date],"30/6"),ALL(Complaints[Received Date])))