Forum Discussion

coding7's avatar
coding7
Frequent Visitor
2 years ago
Solved

Last 12 Months date slicer per default

Hey, 

I am trying to create a date hierarchy slicer, where it has the year and the month and then it has a value called "Last 12 Months". If I click on it, it will show the data for the last 12 months. I have already created the logic:

 

Last12MonthsOrYear = 
VAR TodayDate = TODAY()
VAR StartDate = EOMONTH(TodayDate, -12) + 1
VAR StartYear = YEAR(TodayDate) - 4
RETURN
IF (
    'Date'[Date] >= StartDate && 'Date'[Date] <= TodayDate,
    "Last 12 Months",
    IF(
        YEAR('Date'[Date]) >= StartYear && YEAR('Date'[Date]) <= YEAR(TodayDate),
        FORMAT('Date'[Date], "YYYY"),
        BLANK()
    )
)
TestMonth = IF (
    'Date'[Last12MonthsOrYear] = "Last 12 Months",
    BLANK(),
    FORMAT('Date'[Date], "MMMM")
)

 


and it returns this scenario:



For the "Last 12 Months", I only want it to show the option, but even if it shows blank as it appears, it's not a problem.
The issue is that for 2024 and 2023, it is missing the months that are part of the "Last 12 Months" logic.
For example, if I expand 2024, it doesn't show the months January, February, March, April, May, and June, and for 2023, the same thing, it doesn't show the months July, August, September, October, November, and December because they are part of the "Last 12 Months" logic. I want this to be independent.

 

Is there any way to achieve this logic?

 

The desired output would be: For 2024, to have all options up to the current month (June), and for 2023 to have all months, and then have the option "Last 12 Months" to be independent.

 

This is my Date calculated table:

 

Date = 
var _fromYear=2010 
var _toYear=2030
var _startOfFiscalYear=1 
var _today=TODAY()
return
ADDCOLUMNS(
    CALENDAR(
                DATE(_fromYear,1,1),
                DATE(_toYear,12,31)
),
"Date Key", CONCATENATE(CONCATENATE(YEAR([Date]), FORMAT([Date], "MM")), FORMAT([Date], "dd")),
"Year",YEAR([Date]),
"Start of Year",DATE( YEAR([Date]),1,1),
"End of Year",DATE( YEAR([Date]),12,31),
"Month",MONTH([Date]),
"Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1),
"End of Month",EOMONTH([Date],0),
"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,
"Year Month Number",FORMAT([Date],"YYYY.MM"),
"Year Month Name",FORMAT([Date],"YYYY-MMM"),
"Day",DAY([Date]),
"Day Name",FORMAT([Date],"DDDD"),
"Day Name Short",FORMAT([Date],"DDD"),
"Day of Week",WEEKDAY([Date]),
"Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,
"Month Name",FORMAT([Date],"MMMM"),
"Month Name Short",FORMAT([Date],"MMM"),
"Quarter",QUARTER([Date]),
"Quarter Name","Q"&FORMAT([Date],"Q"),
"Year Quarter Number",INT(FORMAT([Date],"YYYYQ")),
"Year Quarter Name",FORMAT([Date],"YYYY")&" Q"&FORMAT([Date],"Q"),
"Start of Quarter",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1),
"End of Quarter",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0),
"Week of Year",WEEKNUM([Date]),
"Start of Week", [Date]-WEEKDAY([Date])+1,
"End of Week",[Date]+7-WEEKDAY([Date]),
"Fiscal Year",if(_startOfFiscalYear=1,YEAR([Date]),YEAR([Date])+ QUOTIENT(MONTH([Date])+ (13-_startOfFiscalYear),13)),
"Fiscal Quarter",QUARTER( DATE( YEAR([Date]),MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,1) ),
"Fiscal Month",MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,
"Day Offset",DATEDIFF(_today,[Date],DAY),
"Month Offset",DATEDIFF(_today,[Date],MONTH),
"Quarter Offset",DATEDIFF(_today,[Date],QUARTER),
"Year Offset",DATEDIFF(_today,[Date],YEAR)
)

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  coding7 ,

     

    As far as I know, according to the Power BI design, if you don't have data for the corresponding month and you use the created calculated column as a slicer, the slicer will only show the month with data.

    If you use the date hierarchy column as a slicer, months with no data are automatically displayed.

    If you want to keep ‘Last 12 Months’ and hide the blank lines at the same time, you can consider using the custom Visual ‘Hierarchy Slicer’.

    Selection controls – Hide Members -- Empty

     

    Best Regards,

    Liu Yang

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi  coding7 ,

     

    As far as I know, according to the Power BI design, if you don't have data for the corresponding month and you use the created calculated column as a slicer, the slicer will only show the month with data.

    If you use the date hierarchy column as a slicer, months with no data are automatically displayed.

    If you want to keep ‘Last 12 Months’ and hide the blank lines at the same time, you can consider using the custom Visual ‘Hierarchy Slicer’.

    Selection controls – Hide Members -- Empty

     

    Best Regards,

    Liu Yang

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