Forum Discussion
IF statement unexpected result evaluation
Good news! I've been able to figure out that I had a fault in on of my measures.
Fiscal Year TY = var report_FY_Year = CALCULATE(MAX('Calendar'[Fiscal Year]), FILTER(ALL('Calendar'), 'Calendar'[Date] = [Report_Month])) RETURN report_FY_YearThe Fiscal Year calculation did not have ALL('Calendar') in the filter. So when I was convinced that it was returning 2019... it was not necessarily in all cases as it worked through July thru June.
This meant that when I used it in Revenue YTD the measure kept changing on me.
_Revenue_YTD = VAR reportMonth = [Report_Month] VAR fiscalYear = [Fiscal Year TY] VAR YTD_Revenue = CALCULATE(Data[_Revenue], DATESYTD('Calendar'[Date], "30 June"), FILTER(ALL('Calendar'), AND('Calendar'[Fiscal Year]=fiscalYear, 'Calendar'[Date] <= reportMonth))) RETURN YTD_RevenueThis measure does give me the expected results now.
- David
Parameterised Measure - reproduction
Above is a link to a pbix file (including .xls data) that illustrates the challenge of the parameterised measure I'm trying to solve. Top half displays unexpected results. Bottom half displays expected results.
Implementation _very_ closley mirrors my actual implementation.
Data has been "changed" to protect what needs to be protected.
- David
Hi dgwilson
Thanks for the PBIX file below are the DAX Measures that you can create
I created a Cost measure, this makes it easier for when reading the DAX and for when you have to make changes you only need to update 1 measure.
Cost = SUM(Data[Cost])
What the is doing I am using the TOTALYTD function and then defining my end of year being the end of Jun for each year.
Cost YTD = TOTALYTD([Cost],'Calendar'[Date],ALL('Calendar'),"6/30")
Next to get the Previous Year I use the SAMEPERIODLASTYEAR function, which will first look at the measure [Cost YTD] and see how it is defined and what it returns.
By using the SAMEPERIODLASTYEAR it goes back one previous year based on the period in your table.
Cost PY = CALCULATE([Cost YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))
And here is the result below.
The great thing about Time Intelligence features is that they will keep on moving through time and there is no hard coding.
I hope that this helps
GilbertQ Thanks for the excellent reply. And some great tips there too.
I had been using the Time Intelligence functions and perhaps I'd got myself into a bit of a problem.
Possibly the dataset doesn't demonstrate the problem (my fault sorry).
What happens to your YTD function when the date is 4 July?
I've got data, coming in for July now... it will be incomplete until about mid August. What I'm saying is that for reporting reasons (human understanding) the for a period of the next 6 weeks the YTD is 1 June 2018 to 30 July 2019... Which is why in the sample file there is a "Report_Month" measure to address that...
- David
- Hi there
You could put in the Date Slicer and just set the data to stop on 30 Jun 2019, which will not show the data for the new Financial Year.
This also then means that the users can possibly change the fiscal years which they want to compare?To keep the pages clean I'd rather stay away from the date slicer... and I want the Revenue YTD measure to be able to stand on it's own. However this has given me another idea...
On my calendar table I could add a reporting date or something better named... and introduce a filter to the Revenue YTD measure based on this new date.
I'll have to test this.
- David
This is interesting. In my reproduction I'm implementing your solution and the initial (June) results do not match the expected results.
What is also interesting is that I have July 2019 data and the forumla hasn't "flopped" to show YTD data for NOW - i.e. FY20.
- Hi there
When I validated it on the test data the numbers did match
What you could do for the dates is to put it into the Filter, so that it will not clutter the page?
Which then should also make the measures workGood news! I've been able to figure out that I had a fault in on of my measures.
Fiscal Year TY = var report_FY_Year = CALCULATE(MAX('Calendar'[Fiscal Year]), FILTER(ALL('Calendar'), 'Calendar'[Date] = [Report_Month])) RETURN report_FY_YearThe Fiscal Year calculation did not have ALL('Calendar') in the filter. So when I was convinced that it was returning 2019... it was not necessarily in all cases as it worked through July thru June.
This meant that when I used it in Revenue YTD the measure kept changing on me.
_Revenue_YTD = VAR reportMonth = [Report_Month] VAR fiscalYear = [Fiscal Year TY] VAR YTD_Revenue = CALCULATE(Data[_Revenue], DATESYTD('Calendar'[Date], "30 June"), FILTER(ALL('Calendar'), AND('Calendar'[Fiscal Year]=fiscalYear, 'Calendar'[Date] <= reportMonth))) RETURN YTD_RevenueThis measure does give me the expected results now.
- David