Results 1 to 1 of 1

Thread: Income Statement - Year and Month Slicers

  1. #1
    Acolyte ExcelQuestion's Avatar
    Join Date
    May 2018
    Posts
    23
    Articles
    0
    Excel Version
    Office 365

    Income Statement - Year and Month Slicers



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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...?
    Click image for larger version. 

Name:	IS Multiple Years Not Okay.JPG 
Views:	18 
Size:	59.0 KB 
ID:	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
    Click image for larger version. 

Name:	IS One Year is Okay.JPG 
Views:	15 
Size:	48.6 KB 
ID:	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.

    Code:
    =Calculate(
    sum(fTable[Amount]),
    Filter(dCalendar,
    dCalendar[Date]<=MAX(dCalendar[Date])
    )
    )
    Thanks in advance,
    Ricky
    Last edited by ExcelQuestion; 2019-09-27 at 10:41 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •