YTD , when no data for selected period , should fallback to recent available data .

[Gopichakra]

we are calculating a ytd based on fiscal year and period , if there is no data for selected period it should fall back to recent available period ! Amount is sliced in a matrix visual by Area ( A and B) . if Dept A doesnot have data for A for a selected period (3) it should fallback to recent available period (2) . But B does have vaule on period 3 !.

[Gopichakra]

It worked , thanks!

[Kedar_Pande · Accepted solution]

@Gopichakra 

Create a Measure for YTD with Fallback:

YTD with Fallback =
VAR SelectedPeriod = MAX('Date'[Period])
VAR SelectedYear = MAX('Date'[Fiscal Year])
VAR LatestAvailablePeriod =
CALCULATE(
MAX('Date'[Period]),
FILTER(
ALL('Date'),
'Date'[Fiscal Year] = SelectedYear &&
'Date'[Period] <= SelectedPeriod &&
NOT ISBLANK([Amount])
)
)
VAR FallbackPeriod = IF(ISBLANK([Amount]), LatestAvailablePeriod, SelectedPeriod)
RETURN
CALCULATE(
SUM('Sales'[Amount]),
FILTER(
ALL('Date'),
'Date'[Fiscal Year] = SelectedYear &&
'Date'[Period] <= FallbackPeriod
)
)

In the matrix visual, this measure will slice the data by Area. For Dept A, it will automatically use the most recent available period if no data exists for the selected period. For Dept B, it will use the selected period if data is available.

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[Gopichakra]

 would you know how to color the A and B (different colors) also the blank and fall back values . if you use a conditional formatting on Area it leaves out the blank and fallback values .