Forum Discussion
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)
)
- Anonymous2 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
- AnonymousNot 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.