Forum Discussion

dgwilson's avatar
dgwilson
Resolver III
7 years ago
Solved

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 code...
Need Help
  • dgwilson's avatar
    dgwilson
    7 years ago

    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's avatar
GilbertQ
Super User
7 years ago

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's avatar
dgwilson
Resolver III
7 years ago

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's avatar
    GilbertQ
    Super User
    7 years ago
    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's avatar
      dgwilson
      Resolver III
      7 years ago

      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's avatar
        GilbertQ
        Super User
        7 years ago
        Awesome, thanks for letting me know.