IF statement unexpected result evaluation

[dgwilson]

The goal here is to remove a very tiny piece of hard coding so that this measure never needs to be touched again. The measure returns a year to date sum on revenue.

It works if the year is hard coded.

It does not work if the year is another measure. I can with certainty say that the measure [Fiscal Year TY] is returning 2019 as a Whole Number.

I'm seeking recommendations on what I should do next.

_Revenue TY =

    VAR reportMonth = [Report_Month]
    VAR fiscalYear = 2019                        // this works and is returning a value that charts correctly on a line graph (July, August, September)
//    VAR fiscalYear = [Fiscal Year TY]           // returns the value (total) – it does not chart on a line graph (only shows a “dot” for the last month).

RETURN
CALCULATE(TOTALYTD(SUM(EVO_DMT_BUS_CUST_PROFIT_VW[_Revenue]), 'Calendar'[Date], "30 June"), 'Calendar'[Fiscal Year]=fiscalYear, ALL(EVO_DMT_BUS_CUST_PROFIT_VW[RPT_MONTH]), EVO_DMT_BUS_CUST_PROFIT_VW[RPT_MONTH] <= reportMonth)

The results I am receiving are below (the aqua line shoudl be ignored - that's the previous year and another (similar) calculation.

[GilbertQ]

Hi there

The reason would be is because a measure will changed based on where it is used, especially within a variable scope.

What if you had to put it as:
VAR fiscalYear = MAX('TableName'[YearColumn])

[dgwilson]

@GilbertQ - thank you for the reply.

In the original post I had ment to include the measure for Fiscal Year

Fiscal Year TY = 
    var report_FY_Year = CALCULATE(MAX('Calendar'[Fiscal Year]), FILTER('Calendar', 'Calendar'[Date] = [Report_Month]))
RETURN
    report_FY_Year

Which I _think_ is the same as what you are suggesting.

As you've probably guessed I'm dealing with the issue of Financial Year rollover. The accounts for June are not ready yet and will not be for another 5-7 days. So I'm still reporting in Power bI the May result. So given it's now 4 July (here in NZ) I still want to present data for the FY19 financial year. Hence the calculation for Revenue YTD.... and then I want to remove the hard coding.

My Calendar date table has the appropriate FY as a columb for each date.

- David

p.s. the subject heading on this thread is wrong... not sure how that happened. Probably me scrolling/clicking in the wrong place.

[Ashish_Mathur]

Hi,

Please share some raw data to work with.  On the raw data that you share, please also show the expected result in a simple Table.  Once the numbers in the Table are computed correctly, we can always change the visual to a line graph.

[dgwilson]

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.

@Ashish_Mathur 

@GilbertQ 

- David

[GilbertQ]

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

[dgwilson]

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.

[GilbertQ]

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 work

[dgwilson · Accepted solution]

@GilbertQ 

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_Year

The 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_Revenue

This measure does give me the expected results now.

- David

[GilbertQ]

Awesome, thanks for letting me know.

[dgwilson]

@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

[GilbertQ]

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?

[dgwilson]

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