Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am having trouble with a period slicer to display correctly. The weekly, monthly and quartely periods are in working order. But I can not seem to get the daily, yearly to display correct data and the financial year - will not come up as part of the slicer.
The table I have created for my period slicer is:
Can someone please help me with this slicer. Below is a sample of the power bi file and data sample.
Power BI: https://www.dropbox.com/s/8pp0m0vmvjvryce/Coffee%20Attendances%20-%20Copy.pbix?dl=0
Thank you and kudos.
Solved! Go to Solution.
Hi @matrix_user ,
For context to others (as asked by matrix_user):
"My DAX is as below but it is not allowing me to create the table. Please see image also below showing yyyy & yy-yy not accepted:"
"Visual Date",FORMAT([Date],"dd-mmm-yyyy"),
"Type", "Daily",
"Type Order",1,
"Visual Date Order", FORMAT([Date],"dd-mmm-yy")),
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]),MAX('Date'[Date])),
"Visual Date",FORMAT([Date],"yyyy"),
"Type", "Weekly",
"Type Order",2,
"Visual Date Order", FORMAT([Date],"YYYY")),
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]),MAX('Date'[Date])),
"Visual Date",FORMAT([Date],"mmm-yy"),
"Type", "Monthly",
"Type Order",3,
"Visual Date Order", FORMAT([Date],"mmm-yy")),
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]),MAX('Date'[Date])),
"Visual Date",CONCATENATE(YEAR([Date])& "/",QUARTER([Date])),
"Type", "Quarter",
"Type Order",4,
"Visual Date Order",CONCATENATE(YEAR([Date])& "/",QUARTER([Date])),
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]),MAX('Date'[Date])
"Visual Date",FORMAT([Date],"yyyy"),
"Type", "Yearly",
"Type Order",5,
"Visual Date Order", FORMAT([Date],"yyyy")),
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]),MAX('Date'[Date]),
"Visual Date",FORMAT([Date],"yy-yy"),
"Type", "FY",
"Type Order",6,
"Visual Date Order", FORMAT([Date],"yy-yy"))
The date table I am using with this DAX is:
Date = CALENDAR (MIN('Fact table'[date]), MAX('Fact table'[date]))
|
dd-mmm-yy |
Date_Short = FORMAT([Date],"DD/MM/YY")
|
|
Day_Text = FORMAT('Date'[Date], "DDD")
|
|
DayNbr_Year = DATEDIFF ( DATE ( YEAR ( 'Date'[Date] ), 1, 1 ), 'Date'[Date], DAY )+1
|
|
DayYear_Text = LEFT(FORMAT([Date].[Day],"DD"),3) & " " & LEFT([Date].[Month],3) & " " & RIGHT([Date].[Year],2)
|
|
FYear = VAR fy = IF ( MONTH ( 'Date'[Date] ) <= 6, VALUE ( FORMAT ( 'Date'[Date], "YY" ) ) - 1, VALUE ( FORMAT ( 'Date'[Date], "YY" ) ) ) RETURN CONCATENATE ( fy, CONCATENATE ( "-", fy + 1 ) )
|
|
FYear_Sort = IF(MONTH( 'Date'[Date])>6, MONTH('Date'[Date])-6, MONTH('Date'[Date])+6)
|
|
Month = LEFT(FORMAT('Date'[Date],"MMMM"),3) & " " & RIGHT('Date'[Year],4)
|
|
MonthNbr_Year (Sort) = FORMAT('Date'[Date],"YYYYMM")
|
|
MonthYear_Text = LEFT(FORMAT([Date].[Month],"MMM"),3) & " " & RIGHT([Date].[Year],2)
|
|
Quarter = INT(FORMAT('Date'[Date],"Q"))
|
|
QuarterSuffix_Year = FORMAT([Date], "\QrtQ-YY")
|
|
WeekNbr_Month = WEEKNUM([date],1)-WEEKNUM(DATE([date].[Year],[date].[MonthNo],1),1)+1
|
|
WeekNbrSuffix_Year = "W" & WEEKNUM ([Date],2) & " - " & YEAR ( [Date] )
|
|
Year = FORMAT('Date'[Date], "YYYY") |
It seems you are missing ")" in the dax shown in the picture. For creating calendar tables (or in this case dynamic slicer table using calendar as a basis) ADDCOLUMNS + CALENDAR works so that you should close the brackets for CALENDAR before you use ADDCOLUMNS. Basically we are creating a calendar table and then as per the function name suggests we will add custom columns to the table.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Hi @matrix_user ,
For context to others (as asked by matrix_user):
"My DAX is as below but it is not allowing me to create the table. Please see image also below showing yyyy & yy-yy not accepted:"
"Visual Date",FORMAT([Date],"dd-mmm-yyyy"),
"Type", "Daily",
"Type Order",1,
"Visual Date Order", FORMAT([Date],"dd-mmm-yy")),
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]),MAX('Date'[Date])),
"Visual Date",FORMAT([Date],"yyyy"),
"Type", "Weekly",
"Type Order",2,
"Visual Date Order", FORMAT([Date],"YYYY")),
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]),MAX('Date'[Date])),
"Visual Date",FORMAT([Date],"mmm-yy"),
"Type", "Monthly",
"Type Order",3,
"Visual Date Order", FORMAT([Date],"mmm-yy")),
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]),MAX('Date'[Date])),
"Visual Date",CONCATENATE(YEAR([Date])& "/",QUARTER([Date])),
"Type", "Quarter",
"Type Order",4,
"Visual Date Order",CONCATENATE(YEAR([Date])& "/",QUARTER([Date])),
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]),MAX('Date'[Date])
"Visual Date",FORMAT([Date],"yyyy"),
"Type", "Yearly",
"Type Order",5,
"Visual Date Order", FORMAT([Date],"yyyy")),
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]),MAX('Date'[Date]),
"Visual Date",FORMAT([Date],"yy-yy"),
"Type", "FY",
"Type Order",6,
"Visual Date Order", FORMAT([Date],"yy-yy"))
The date table I am using with this DAX is:
Date = CALENDAR (MIN('Fact table'[date]), MAX('Fact table'[date]))
|
dd-mmm-yy |
Date_Short = FORMAT([Date],"DD/MM/YY")
|
|
Day_Text = FORMAT('Date'[Date], "DDD")
|
|
DayNbr_Year = DATEDIFF ( DATE ( YEAR ( 'Date'[Date] ), 1, 1 ), 'Date'[Date], DAY )+1
|
|
DayYear_Text = LEFT(FORMAT([Date].[Day],"DD"),3) & " " & LEFT([Date].[Month],3) & " " & RIGHT([Date].[Year],2)
|
|
FYear = VAR fy = IF ( MONTH ( 'Date'[Date] ) <= 6, VALUE ( FORMAT ( 'Date'[Date], "YY" ) ) - 1, VALUE ( FORMAT ( 'Date'[Date], "YY" ) ) ) RETURN CONCATENATE ( fy, CONCATENATE ( "-", fy + 1 ) )
|
|
FYear_Sort = IF(MONTH( 'Date'[Date])>6, MONTH('Date'[Date])-6, MONTH('Date'[Date])+6)
|
|
Month = LEFT(FORMAT('Date'[Date],"MMMM"),3) & " " & RIGHT('Date'[Year],4)
|
|
MonthNbr_Year (Sort) = FORMAT('Date'[Date],"YYYYMM")
|
|
MonthYear_Text = LEFT(FORMAT([Date].[Month],"MMM"),3) & " " & RIGHT([Date].[Year],2)
|
|
Quarter = INT(FORMAT('Date'[Date],"Q"))
|
|
QuarterSuffix_Year = FORMAT([Date], "\QrtQ-YY")
|
|
WeekNbr_Month = WEEKNUM([date],1)-WEEKNUM(DATE([date].[Year],[date].[MonthNo],1),1)+1
|
|
WeekNbrSuffix_Year = "W" & WEEKNUM ([Date],2) & " - " & YEAR ( [Date] )
|
|
Year = FORMAT('Date'[Date], "YYYY") |
It seems you are missing ")" in the dax shown in the picture. For creating calendar tables (or in this case dynamic slicer table using calendar as a basis) ADDCOLUMNS + CALENDAR works so that you should close the brackets for CALENDAR before you use ADDCOLUMNS. Basically we are creating a calendar table and then as per the function name suggests we will add custom columns to the table.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Hi,
I cleaned up the DAX but it has moved on to detect other errors to do with month.
Hi @matrix_user ,
You can refer the following links to get it:
Custom Date Period Selections in Power BI
Slicer with selection of Date Periods (Year, Quarter, Month) that come from multiple columns
Dynamic Date Slicer including a Week and Quarter option
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |