locked
Parameterized FYTD and LFYTD measures RRS feed

  • Question

  • Hi,

    is there a way to create parameterized FYTD and LFYTD measures and avoid hardcoding fiscal year end date in the DATESYTD function ?

    I have tried using both VALUES and LOOKUPVALUE to retrieve fiscal year end date inside DATESYTD, but it apparantly only accepts a literal.

    I have created a Calendar table with e.g. FiscalYearIndex which is 0 for current fiscal year dates and was thinking somthing with CALCULATE and a filtered Calendar table based on FiscalYearIndex = 0.

    Best regards,

    Jesper Bork

    Friday, July 10, 2015 2:22 PM

Answers

  • Hi Jseper,

    According to your description, you need to create parameterized FYTD and LFYTD measures, right?

    Generally, for the Fiscal Year to Date, we can create a date table with all dates from before your min date to somewhere after your max date. Then create calculate column and measures. The sample DAX expression below is for you reference.
    FiscalMonthNumber:=IF('Date'[Month] <= 6,'Date'[Month] + 6,'Date'[Month] - 6)
    Total:=SUM(TABLE[COLUMN])
    Total_YTD:=CALCULATE([Total],DATESYTD('Date'[date],"6/31"))

    Here is a thread which similar to your, please refer to the link below.
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/525c8a24-63a9-4b4e-8689-340ce7e41856/fiscal-year-to-date

    If this is not what you want, please provide us more information, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Friday, July 17, 2015 5:00 PM
    • Marked as answer by Charlie Liao Tuesday, August 4, 2015 7:51 AM
    Tuesday, July 14, 2015 5:39 AM

All replies

  • Hi Jesper,

    The fiscal year end date is supposed to be fixed. For DATESYTD and TOTALYTD you only have to provide the day and month for the end date, and you can use that for any year in your data. So if your reporting context is July 2015, DATESYTD with end date "31/12" will return all dates from 1/1/2015 until 31/7/2015. If your report context is April 2014, the result will be all dates from 1/1/2014 until 30/4/2014.

    Friday, July 10, 2015 4:11 PM
    Answerer
  • Hi Jesper,

    Have you already considered using a variation of the custom time intelligence approach described here: http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, July 11, 2015 12:06 PM
  • Hi Jseper,

    According to your description, you need to create parameterized FYTD and LFYTD measures, right?

    Generally, for the Fiscal Year to Date, we can create a date table with all dates from before your min date to somewhere after your max date. Then create calculate column and measures. The sample DAX expression below is for you reference.
    FiscalMonthNumber:=IF('Date'[Month] <= 6,'Date'[Month] + 6,'Date'[Month] - 6)
    Total:=SUM(TABLE[COLUMN])
    Total_YTD:=CALCULATE([Total],DATESYTD('Date'[date],"6/31"))

    Here is a thread which similar to your, please refer to the link below.
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/525c8a24-63a9-4b4e-8689-340ce7e41856/fiscal-year-to-date

    If this is not what you want, please provide us more information, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Friday, July 17, 2015 5:00 PM
    • Marked as answer by Charlie Liao Tuesday, August 4, 2015 7:51 AM
    Tuesday, July 14, 2015 5:39 AM