none
TOTALYTD Issue When Trying to Use One Measure (with a switch) When Some Units Use Calendar Year-end and others use Fiscal Year-ends RRS feed

  • Question

  • So the issue that we are running into deals with calculating YTD properly when some business units are on a different fiscal year. Some business units are Calendar Year and others are Fiscal Year (year end of 6/30). What we did for the setup is as follows:
    We created a “Year End” column in the Business Unit dimension table. This denotes either a year end of 6/30 or a year end of 12/31. Next, in our fact data table (Profit and Loss) I created a LOOKUPVALUE column that looks up the value (6/30 or 12/31 from the Business Unit dimension table) and another column called YearEndSwitch (either a 1 or a 2 based on 6/30 or 12/31). I cannot use this Switch in an IF statement, so I created a hidden YearEndSwitchMeasure that uses the AVERAGE calculation to give me either a 1 or a 2 in an IF statement.

    Example:
    Dollars Actual YTD:=if([YearEndSwitchMeasure]=1, TOTALYTD([Dollars Actual], 'Time'[ApplyDate], "06/30"), TOTALYTD([Dollars Actual], 'Time'[ApplyDate], "12/31"))

    The issue here is that I will only get a YearEndSwitchMeasure value of 1 or a 2 *if* there is an actual row value present. So for example, if we have a Dollar amount in a certain account for a certain business unit for January, the YearEndSwitchMeasure will assign a 1 or a 2 based on Year End. But if there is no value for February, TOTALYTD will not calculate correct because the IF statement cannot determine if it is a 1 or a 2 (because it is actually a BLANK!)
    We are trying to avoid having to use two separate YTD Measures based on 6/30 or 12/31 Year End. We would like to use a single YTD measure, but this seems difficult since we cannot get a switch to work to automatically determine which one a business unit is using.
    We have tried using LOOKUPVALUE but that does not work in the context of a measure.

    Thanks in advance!

    Rich

    6.3.6
    Friday, November 15, 2019 4:11 PM

Answers

  • I did not realise you have multiple copies of the year end column. If you are filtering on the 'Location' table you should be using this in the call to SELECTEDVALUE. For this calc I would not be duplicating this column over the the 'Profit and Loss' table.

    eg.

    Dollars Actual YTD:=VAR _endofYear = SELECTEDVALUE('Location'[YearEnd]) RETURN SWITCH(_endofYear, "6/30",[Dollars Actual YTD (06/30)],"12/31",[Dollars Actual YTD (12/31)],BLANK())

    I'm not sure if this will make a difference or not. It depends on your other relationships and which tables the [Dollars Actual] measure is pulling from. You should be able to create a test measure like the following to see the YearEnd value. 

    TEST:= SELECTEDVALUE('Location'[YearEnd]) 

    If I do this in a simple test model it works fine.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Scullpa Monday, November 25, 2019 2:25 PM
    Friday, November 22, 2019 6:15 AM
    Moderator

All replies

  • So you can use LOOKUPVALUE in a measure, but you just have to be careful not to use it in a context where there might be multiple values. If you are using SSAS 2017 or later you can use the SELECTEDVALUE function. 

    eg.

    Dollars Actual YTD:=
    VAR _endOfYear = SELECTEDVALUE( 'Business Unit'[Year End])
    RETURN SWITCH( _endOfYear,
    "6/30", TOTALYTD([Dollars Actual], 'Time'[ApplyDate], "06/30"),
    "12/31", TOTALYTD([Dollars Actual], 'Time'[ApplyDate], "12/31"),
    BLANK())

    Otherwise you would have to wrap the LOOKUPVALUE in an IF check

    eg.

    Dollars Actual YTD:=
    IF( HASONEVALUE( 'Business Unit'[Year End]) ,
     SWITCH( LOOKUPVALUE( 'Business Unit'[Year End]),
    "6/30", TOTALYTD([Dollars Actual], 'Time'[ApplyDate], "06/30"), 
    "12/31", TOTALYTD([Dollars Actual], 'Time'[ApplyDate], "12/31")),
    BLANK())

    The other consideration here is what should happen when a user has multiple Business Units selected? I'm assuming that it does not make sense to return a YTD if there could potentially be different amount of months so am returning blank() in this situation.


    http://darren.gosbell.com - please mark correct answers

    Sunday, November 17, 2019 11:44 PM
    Moderator
  • Unfortunately, this does not seem to have worked for me.  My exact syntax is as follows:

    Dollars Actual YTD:=VAR _endofYear = SELECTEDVALUE('Profit and Loss'[YearEnd]) RETURN SWITCH(_endofYear, "6/30",[Dollars Actual YTD (06/30)],"12/31",[Dollars Actual YTD (12/31)],BLANK())

    I'm not getting any values for this measure.  I have created the 2 separate measures to calculate both a 06/30 YTD and a 12/31 YE amount that shows what should be returned if the location/business unit is either a 6/30 YE or 12/31 YE (image below) but whenever I include that calculation in the above measure it does not return any values.  'Profit and Loss'[YearEnd] by the way is a column in my table.  My original design was to actually reference the [YearEnd] in my location/business unit table but that did not work either.

    https://1drv.ms/u/s!AtmX4PJaNsshzWW1zxWhgSIaAYIy?e=eE2WWn

    Thanks!!

    Rich

    6.4.1
    Wednesday, November 20, 2019 5:24 PM
  • So in that pivot table you've posted I can't see any selection for Business Unit, so the SELECTEDVALUE will return blank and therefore the SWITCH statement will return blank. This is by design as I was assuming you must be building reports that were filtered to a single Business Unit.

    Otherwise you end up with the situation where in January for example you will have 1 month of data for some transactions and 7 months for others - it does not make any sense to me to mix date grain like this. 

    Or was that actually the behaviour you were after?


    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 20, 2019 8:09 PM
    Moderator
  • I'm sorry I just didn't include the top portion of the pivot table in my screen shot.  We use Location and Business Unit in the same context.  I.e. no difference.

    

    6.4.1

    6.4.1
    • Edited by Scullpa Wednesday, November 20, 2019 8:39 PM
    Wednesday, November 20, 2019 8:38 PM
  • OK so that maybe means that there is something different going on in your data that I have not anticipated. What do you see if you drag the 'Business Unit'[YearEnd] column onto the rows of the Pivot table. Does it show only values against "6/30" or "12/31" or does it show values against both?

    http://darren.gosbell.com - please mark correct answers

    Thursday, November 21, 2019 8:47 AM
    Moderator
  • It shows both as I have no switches on the 6/30 and 12/31 columns.  I did that on purpose so that I knew what value(s) I was trying to see.  Only the Dollars Actual YTD column has the SELECTEDVALUE/SWITCH in it.  Here are all of the measures:

    Dollars Actual YTD:=VAR _endofYear = SELECTEDVALUE('Profit and Loss'[YearEnd]) RETURN SWITCH(_endofYear, "6/30",[Dollars Actual YTD (06/30)],"12/31",[Dollars Actual YTD (12/31)],BLANK())

    Dollars Actual YTD (06/30):=TOTALYTD([Dollars Actual],'Time'[ApplyDate],"06/30")

    Dollars Actual YTD (12/31):=TOTALYTD([Dollars Actual],'Time'[ApplyDate],"12/31")

    Just an FYI - Starting at noon ET today, I will be traveling the next 4 days with limited access.  I just wanted to let you know that I'm not ignoring you.  :)

    Thanks!

    Rich

    6.4.1
    Thursday, November 21, 2019 3:06 PM
  • Some other visuals from VS that may help?  (1) PnL table which shows us looking us the YearEnd value from the Location table.  (2) The location table showing one instance of the location as well as the YearEnd value.

    6.4.1
    Thursday, November 21, 2019 3:23 PM
  • I did not realise you have multiple copies of the year end column. If you are filtering on the 'Location' table you should be using this in the call to SELECTEDVALUE. For this calc I would not be duplicating this column over the the 'Profit and Loss' table.

    eg.

    Dollars Actual YTD:=VAR _endofYear = SELECTEDVALUE('Location'[YearEnd]) RETURN SWITCH(_endofYear, "6/30",[Dollars Actual YTD (06/30)],"12/31",[Dollars Actual YTD (12/31)],BLANK())

    I'm not sure if this will make a difference or not. It depends on your other relationships and which tables the [Dollars Actual] measure is pulling from. You should be able to create a test measure like the following to see the YearEnd value. 

    TEST:= SELECTEDVALUE('Location'[YearEnd]) 

    If I do this in a simple test model it works fine.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Scullpa Monday, November 25, 2019 2:25 PM
    Friday, November 22, 2019 6:15 AM
    Moderator
  • Darren-

    using SELECTEDVALUE worked!!  So we ended up with this:

    =if(SELECTEDVALUE('Location'[YearEnd])="06/30", TOTALYTD([Dollars Actual], 'Time'[ApplyDate], "06/30"), TOTALYTD([Dollars Actual], 'Time'[ApplyDate], "12/31"))

    Thank you so much for your help and your patience!!!  I really appreciate it!!!!

    Rich

    6.4.1

    6.4.1
    • Edited by Scullpa Monday, November 25, 2019 3:01 PM
    Monday, November 25, 2019 2:31 PM