none
TotalYTD for dynamic fiscal years RRS feed

  • Question

  • Hi,

    i have a scenario wherein i need to change the report based on the fiscal year of a customer. 

    Customers keep purchasing from me over the year. I need to show the customers their report and need to indicate the report based on their fiscal years. e.g. Fiscal year for customer A may be Jul to Jun and for customer B may be Apr to Mar and for customer C may have Sep to Aug. In cases where the customer has not defined his fiscal year, it is assumed to be Jan to Dec. Hence the fiscal year start and end is created as a measure.

    When i use the TotalYTD function, i have to send as hardcoded 4th parameter viz. either "30/6" or "31/03" or "31/08" and so on.

    I am not able to pass a measure as the 4th parameter to the TotalYTD function

    e.g. YTDSales = TOTALYTD (

        [SalesAmount],
        'Date'[Date],

        CustomerFiscalEnd)

    Is there any workaround for the same?

    Please help.

    Rgds,

    Rajeev

    Monday, November 19, 2018 3:33 PM

All replies

  • You could do it all dynamically, however, I would encourage you to add in the multiple FY attributes to the date dimension. Essentially, in the Date table/view add a set of columns (FY, FQ, FM, FW) for each of the fiscal years. These columns will simply be attributes in the Date dimension, which are typically not visible, and you create a hierarchy for each of the fiscal years. So, you will have a hierarchy for FY Jul -Jun, FY Apr -Mar etc.

    This blog explains the same concept but for weeks commencing on different days. http://richardlees.blogspot.com/2015/08/supporting-multiple-first-day-of-week.html Use the same technique for multiple fiscal years.

    Hope that helps,


    Richard

    Monday, November 19, 2018 9:55 PM
  • One "trick" for making this dynamic would be to use a switch statement as there should only be a finite number of year ends. However you'd also need to figure out how to handle when multiple customers are selected. 

    eg.

    YTDSales =
    SWITCH( [CustomerFiscalEnd].
    "30/6", TOTALYTD( [SalesAmount], 'Date'[Date], "30/6"),
    "31/7", TOTALYTD( [SalesAmount], 'Date'[Date], "31/7"),
    "31/8", TOTALYTD( [SalesAmount], 'Date'[Date], "31/8"),
    ...

    )


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

    Tuesday, November 20, 2018 6:28 AM
    Moderator
  • I think we can add nothing to the Date table for this task and just add a single column to the Customer table, holding the fiscal year end string parameter. Then, a (hidden) YTDSalesHelper measure could start with SWITCH ( VALUES ( 'Customer'[FYend] )..., and the final YTDSales could be 
    SUMX ( VALUES ( 'Customer'[FYend] ),
           [YTDSalesHelper]
    )
    If there can never be more than one fiscal year end value in a report, the 'helper' measure can be used as the only and final one.

    Expect me to help you solve your problems, not to solve your problems for you.

    • Proposed as answer by Pirlo Zhang Tuesday, November 27, 2018 2:17 AM
    Tuesday, November 20, 2018 7:42 AM