Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Can someone please help
I have a date slicer with start and end dates.I want to find the first working day of month for each months tat comes in that slicer start n end dates.For each first working day,i want to execute a specific logic.How can i do it dynamically.
If my slicer is
4/4/2018 to 6/6/2018
I want to find out the first working day of May and June. and for these two first working days i wnat execute a specifc logic.
If my slicer is
1/4/2018 to 6/6/2018
then first working day of April,May and June. and for these 3 first working days i want to execute a specific log.
Solved! Go to Solution.
HI @Dain,
If you mean use slicer to get dynamic calculate table, it is impossible.
You can try to use two calendar to dynamic filter first work day.(tables not contains relationship)
Steps:
1. Use calendar to create slicer, calendar2 to create table visual.
2. Write a measure to check current row contents if it suitable requirement.
First Working Day = VAR _current = SELECTEDVALUE ( 'CALENDAR2'[Date] ) VAR _firstWeekDay = MINX ( FILTER ( ADDCOLUMNS ( CALENDAR ( DATE ( YEAR ( _current ), MONTH ( _current ), 1 ), DATE ( YEAR ( _current ), MONTH ( _current ) + 1, 1 ) - 1 ), "Day of Week", WEEKDAY ( [Date], 1 ) ), [Day of Week] <> 1 && [Day of Week] <> 7 ), [Date] ) RETURN IF ( _current IN ALLSELECTED ( 'CALENDAR'[Date] ), IF ( _current = _firstWeekDay, 1, 0 ), 0 )
BTW, if you only want to export suitable date string based on one calendar, you can refer to below formula.
Suitable Working Day = VAR temp = FILTER ( ADDCOLUMNS ( ALLSELECTED ( 'CALENDAR'[Date] ), "Year Month", FORMAT ( [Date], "mm/yyyy" ) ), WEEKDAY ( 'CALENDAR'[Date], 1 ) <> 1 && WEEKDAY ( 'CALENDAR'[Date], 1 ) <> 7 ) RETURN CONCATENATEX ( SUMMARIZE ( temp, [Year Month], "FirstDate", MINX ( FILTER ( temp, [Year Month] = EARLIER ( [Year Month] ) ), [Date] ) ), [FirstDate], "," )
Regards,
Xiaoxin Sheng
HI @Dain,
If you mean use slicer to get dynamic calculate table, it is impossible.
You can try to use two calendar to dynamic filter first work day.(tables not contains relationship)
Steps:
1. Use calendar to create slicer, calendar2 to create table visual.
2. Write a measure to check current row contents if it suitable requirement.
First Working Day = VAR _current = SELECTEDVALUE ( 'CALENDAR2'[Date] ) VAR _firstWeekDay = MINX ( FILTER ( ADDCOLUMNS ( CALENDAR ( DATE ( YEAR ( _current ), MONTH ( _current ), 1 ), DATE ( YEAR ( _current ), MONTH ( _current ) + 1, 1 ) - 1 ), "Day of Week", WEEKDAY ( [Date], 1 ) ), [Day of Week] <> 1 && [Day of Week] <> 7 ), [Date] ) RETURN IF ( _current IN ALLSELECTED ( 'CALENDAR'[Date] ), IF ( _current = _firstWeekDay, 1, 0 ), 0 )
BTW, if you only want to export suitable date string based on one calendar, you can refer to below formula.
Suitable Working Day = VAR temp = FILTER ( ADDCOLUMNS ( ALLSELECTED ( 'CALENDAR'[Date] ), "Year Month", FORMAT ( [Date], "mm/yyyy" ) ), WEEKDAY ( 'CALENDAR'[Date], 1 ) <> 1 && WEEKDAY ( 'CALENDAR'[Date], 1 ) <> 7 ) RETURN CONCATENATEX ( SUMMARIZE ( temp, [Year Month], "FirstDate", MINX ( FILTER ( temp, [Year Month] = EARLIER ( [Year Month] ) ), [Date] ) ), [FirstDate], "," )
Regards,
Xiaoxin Sheng