Forum Discussion
IF statement unexpected result evaluation
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.
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
12 Replies
- 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])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_YearWhich 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.
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.
