Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Problems with Fiscal and Calendar YTD in same TOTALYTD calc

  • 1.  Problems with Fiscal and Calendar YTD in same TOTALYTD calc

    Posted 23 days ago
    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.

    Thank you for any help!

    ------------------------------
    Robert Bujdoso
    Senior BI Developer
    Complete Healthcare Resources
    Dresher PA
    2153285715
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Problems with Fiscal and Calendar YTD in same TOTALYTD calc

    Top Contributor
    Posted 23 days ago
    HI Robert !

    Can you please share your Data Model Diagram as well, i am trying to envision how you link your Fact & Dimension

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Director Data & Analytics
    Karachi, Pakistan
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Problems with Fiscal and Calendar YTD in same TOTALYTD calc

    Posted 22 days ago
    Edited by Robert Bujdoso 22 days ago
    Thank you for your reply, Hasham!

    The model looks like this:

    Time dimension
    -Date (linked to Fact table via Posting Date)
    -Month
    -Year

    Business Unit dimension
    -Business Unit (linked to Fact table via Company)
    -Company description/name
    -Address, etc.
    -Year End (6/30 or 12/31)

    Fact table
    -Account
    -Posting date
    -description
    -Amount
    -Company/Business Unit

    This will work if I use two separate YTD measures with either 6/30 or 12/31 hard-coded in the TOTALYTD function. I was hoping there would be a dynamic way for it to determine (based on the current context company) which year end to use in one YTD measure.

    ------------------------------
    Robert Bujdoso
    Senior BI Developer
    Complete Healthcare Resources
    Dresher PA
    2153285715
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Problems with Fiscal and Calendar YTD in same TOTALYTD calc

    Posted 22 days ago

    Hi Robert,

    we had similar problems in Datawarehouses before - did you ever think about a solution which is not based on the measure calculation but more on the design of the date table.

    If you would just have 1-12 in it and than depending on the Financial Year the Monthnames.  you could refer to a lookup table which has the translation from calender month to Financial month in seperate columns - than the previos year and the YTD function will work on the financial month number. 

    I never tried this in a PBI model but maybe just food for thought and worth a try. 

    BR

    Andreas



    ------------------------------
    Andreas Bollongino
    Owner
    Reichenau
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Problems with Fiscal and Calendar YTD in same TOTALYTD calc

    Posted 22 days ago
    Hi Andreas, thank you for the reply!

    Unfortunately, I believe that I can use only one Date column at a time for TOTALYTD() function. A possible solution is to have two YTD measures: a Dollars Actual YTD Fiscal and a Dollars Actual YTD Calendar. The problem is this solution is not as clean as having only one Dollars Actual YTD measure, because the end user will need to determine which measure to use (either fiscal or calendar) based on the company they are looking at. It is possible the end user may not know whether the company is a fiscal or a calendar year. To make matters more confusing, if they have Company on either row or column in a pivot table, which measure to use? since half the companies are on calendar and the other half are on fiscal.

    ------------------------------
    Robert Bujdoso
    Senior BI Developer
    Complete Healthcare Resources
    Dresher PA
    2153285715
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: Problems with Fiscal and Calendar YTD in same TOTALYTD calc

    Bronze Contributor
    Posted 19 days ago
    HI Robert,

    It seems that you have got 2 measures YTD Dec and YTD Jun.

    I suggest using SWITCH to make the choice for you, as you also have a flag in your Business Unit table, but I would change it to have the full date, then you can use an expression on it.

    YTD Amount = SWITCH(Month(BusinessUnit[YearEnd],6,[YTD Jun], [YTD Dec])

    When you put that measure into a Matrix (or visual) with the Business Unit as a row, or column, It will figure out which YTD Measure to use.

    Hope that helps.

    ------------------------------
    Neville Howard
    Consultant
    Brisbane
    0732228400
    ------------------------------

    Conference-PBI_200x200