YTD Measure in Power BI Using Fiscal Year Dates

[nunezjo]

Hello Power BI community,

I'm currently facing a challenge when calculating a Year-to-Date measure using my organization's custom fiscal year dates in Power BI.

I have a date dimension table (DT_DIM) which contains three date fields:

1. DAY_DT: Contains all dates using the calendar year format.

2. FCL_YR_BEGN_DT: Contains the beginning date for each fiscal year in date-time format (e.g., 2/3/23 for fiscal year 2023).

3. FCL_YR_END_DT: Contains the end date of each fiscal year, though I am not currently using this column for my YTD calculations.

My current fiscal year does not begin on January 1st, but on specific dates like February 3rd (as in the 2023 example above).

My goal is to create a dynamic YTD measure that starts from the beginning date of the latest fiscal year (from FCL_YR_BEGN_DT) and ends on the latest available date in DAY_DT (which effectively gives the end of the current fiscal year since it's always updated to be the latest date). I've written the following measure for this purpose:

YTD Script Ct (Dynamic) = 
    VAR selectedmeasure = 
    IF(
        SELECTEDVALUE('PARM Gross/Net Script Ct'[PARM Gross/Net Script Ct  Order]) = 1, 
        [Script Count (Excluding Cancels)], 
        [Gross Script Count]
    )

    VAR LatestFiscalYearBegin = CALCULATE(MAX('DT_DIM'[FCL_YR_BEGN_DT]), ALL('DT_DIM'))
    VAR LatestAvailableDate = CALCULATE(MAX('DT_DIM'[DAY_DT]), ALL('DT_DIM'))

    RETURN 
        CALCULATE(
            selectedmeasure,
            FILTER(
                ALL('DT_DIM'),
                'DT_DIM'[DAY_DT] >= LatestFiscalYearBegin && 'DT_DIM'[DAY_DT] <= LatestAvailableDate
            )
        )

However, I'm facing two main issues:

1. The measure seems to be returning the script count for all fiscal years, not just the current fiscal year (from the latest FCL_YR_BEGN_DT to today). It seems like the filter on dates is not working as expected.

2. When I use slicers for fiscal weekend and fiscal period end in my report, selecting a specific year, weekend, or period end causes my YTD measure to show up as blank. I understand that the slicers are affecting the context in which my measure is calculated, but my intent is for the YTD measure to always calculate from the beginning of the fiscal year up to the latest date, regardless of other filters in the report.

Any guidance or suggestions on how to resolve these issues would be greatly appreciated.

additonal measures seen above: 

Gross Script Count = SUM(LDRSHP_RPT_FAC[GRSS_SCRP_CNT])

1. DAY_DT: Date Dimension

   • Format: Date and Time (e.g., 1/31/2021 12:00:00 AM)
   • Data Type: Date/Time

2. FCL_YR_BEGN_DT: Fiscal Year Begin Date Dimension

   • Format: Date (e.g., 1/31/2021)
   • Data Type: Date

3. FCL_YR_ID: Fiscal Year ID Dimension

   • Format: Numeric (e.g., 2021)
   • Data Type: Integer or Numeric

4. FCL_YR_LY_ID: Last Year's Fiscal Year ID Dimension

   • Format: Numeric (e.g., 2020)
   • Data Type: Integer or Numeric

5. Script Count (Excluding Cancels): Script Count Dimension

   • Format: Numeric (whole number)
   • Data Type: Integer or Numeric

6. Gross Script Count: Gross Script Count Dimension

   • Format: Numeric (whole number)
   • Data Type: Integer or Numeric

7. PARM Gross/Net Script Ct[PARM Gross/Net Script Ct Order]: Parameter for Gross/Net Script Count Order

   • Format: Numeric (e.g., 1 or 0)
   • Data Type: Integer or Numeric

   

[Ahmedx]

Share sample pbix file to help you.

[nunezjo]

@amitchandak @truptis @Ritaf1983   @Ashish_Mathur @danextian @Ahmedx 

can someone help me with this, it would be very much appreciated it, this question has been open for a while. 

[Greg_Deckler]

@nunezjo You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also, this video: