Income Statement - Year and Month Slicers

ExcelQuestion

New member
Joined
May 27, 2018
Messages
26
Reaction score
0
Points
0
Excel Version(s)
Office 365
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...?
IS Multiple Years Not Okay.JPG

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
IS One Year is Okay.JPG


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.

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

Thanks in advance,
Ricky
 
Last edited:
Back
Top