Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
DAY_DT: Contains all dates using the calendar year format.
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).
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:
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.
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:
DAY_DT: Date Dimension
FCL_YR_BEGN_DT: Fiscal Year Begin Date Dimension
FCL_YR_ID: Fiscal Year ID Dimension
FCL_YR_LY_ID: Last Year's Fiscal Year ID Dimension
Script Count (Excluding Cancels): Script Count Dimension
Gross Script Count: Gross Script Count Dimension
PARM Gross/Net Script Ct[PARM Gross/Net Script Ct Order]: Parameter for Gross/Net Script Count Order
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |