Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dynamically Add 1 Year Based on Slicer Selections

I am using a Switch Calendar table to create slicers to switch between calendar types and year.   My fiscal calendar begins on July 1st.

How to set the Month/Year = format([Date],"mmmm yyyy") column + one year ahead for dates after 6-30-YYYY only when the Fiscal Slicer is selected?  

 

In this example below, I need to show “MM  2025” dynamically.

 

Switch Calendar =
var startmonth = 7
return
union(
ADDCOLUMNS(
SUMMARIZE('Date','Date'[Date]),
"Year", if(month([Date])<startmonth,date(year([Date]),1,1),date(year([Date])+1,1,1)),
"Calendar", "Fiscal"
)
,
ADDCOLUMNS(
SUMMARIZE('Date','Date'[Date]),
"Year", date(year([Date]),1,1),
"Calendar", "Calendar"
))

 

Date = CALENDAR (DATE (2024, 1, 1), DATE (2024, 12, 31))

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from lbendlin, please allow me to provide another insight.
    Hi Anonymous ,

    You can create a Month/Year calculated column in the Switch Calendar table.

    IF (
    'Switch Calendar'[Calendar] = "Calendar",
    FORMAT ( 'Switch Calendar'[Date], "mmmm yyyy" ),
    FORMAT ( 'Switch Calendar'[Date], "mmmm" ) & " "
        & IF (
            MONTH ( 'Switch Calendar'[Date] ) > 6,
            YEAR ( 'Switch Calendar'[Date] ) + 1,
            YEAR ( 'Switch Calendar'[Date] )
        )
)


    Then use this field to create a table visual. The final result is as follows.

     

    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    Your calendar table needs to cover both the origin and the result of the time intelligence functions.  Did you mark  the "Date"  table as a date table?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from lbendlin, please allow me to provide another insight.
    Hi Anonymous ,

    You can create a Month/Year calculated column in the Switch Calendar table.

    IF (
    'Switch Calendar'[Calendar] = "Calendar",
    FORMAT ( 'Switch Calendar'[Date], "mmmm yyyy" ),
    FORMAT ( 'Switch Calendar'[Date], "mmmm" ) & " "
        & IF (
            MONTH ( 'Switch Calendar'[Date] ) > 6,
            YEAR ( 'Switch Calendar'[Date] ) + 1,
            YEAR ( 'Switch Calendar'[Date] )
        )
)


    Then use this field to create a table visual. The final result is as follows.

     

    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.