PDA

View Full Version : Income Statement - Year and Month Slicers



ExcelQuestion
2019-09-27, 11:37 PM
Good day Experts,
Pounding head to the wall this week...Google searches have been empty. For Income Statement reporting, trying to show all 12 months for previous years.

There are 2 slicers dCalendar[Fiscal Year Text] and dCalendar[Month Text], which is preferred, for the visual check with less scrolling over Dropdown selections & Timelines.

When multiple dCalendar[Fiscal Year Text] selections are made and dCalendar[Month Text] have partial selections, what's the measure to...?
9383

a) keep the filter for the MAX(dCalendar[Fiscal Year Text]) "2019/20" and keep the months filter "Jun", "Jul", "Aug" apply only for the MAX(dCalendar[Fiscal Year Text])
b) for all previous years selected, IS NOT MAX (dCalendar[Fiscal Year Text]) "2019/20" then show all 12 months "Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Sep"

On a side note, if only 1 dCalendar[Fiscal Year Text] is selected, then the months "Jun", "Jul", "Aug" is okay
9382


I've tried this but won't work. Previous years are still governed by the Month Text slicer (shown within the 1st image). Using PowerPivot Office 365.



=Calculate(
sum(fTable[Amount]),
Filter(dCalendar,
dCalendar[Date]<=MAX(dCalendar[Date])
)
)


Thanks in advance,
Ricky