Forum Discussion
Filtering data table based on select month +1 months and consecutive years
Hi Experts,
I am having a data set and it contains last 1 year data , If i select june month in slicer then it should show selected month +1 and consequite available dates.
eg Data sets
Date colum filter applied ; 08/01/2024 - table should display September month data and the consequite available dates.
Expected Output :
SubProcess Country Reason Jog group #FTE UtilizationDate
CA India Efficiency 5 -1 9/1/2024
CA US Efficiency 4 -1 9/1/2024
Ds UK Business Growth 6 1 12/1/2024
Ds UK Efficiency 6 -1 12/31/2024
Hi PBILearner2022 ,
Based on your description, I created these data.
1. create a date table to be used as a slicer.
DAX DateTable = ADDCOLUMNS ( CALENDAR(DATE(2024,1,1), DATE(2024,12,31)), // CALENDARAUTO(), "Year", YEAR([Date]), "Quarter", "Q" & FORMAT(CEILING(MONTH([Date])/3, 1), "#"), "Quarter No", CEILING(MONTH([Date])/3, 1), "Month No", MONTH([Date]), "Month Name", FORMAT([Date], "MMMM"), "Month Short Name", FORMAT([Date], "MMM"), "Month Short Name Plus Year", FORMAT([Date], "MMM,yy"), "DateSort", FORMAT([Date], "yyyyMMdd"), "Day Name", FORMAT([Date], "dddd"), "Details", FORMAT([Date], "dd-MMM-yyyy"), "Day Number", DAY ( [Date] ) )2. Create the measure.
Measure = IF(MAX('Table'[UtilizationDate]) >= EOMONTH(SELECTEDVALUE('DAX DateTable'[Date]),0) + 1, 1)3. Drag the measure into the filter pane of the table visual object to filter the data with the value of 1.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
PBILearner2022 do you have a dimDate table yet?
Excel with Allison: DimDate: What, Why and How?
You can use time intelligence functions to shift the date filters. Then you'll just need to create a new measure using those functions. Looking at your post, my guess is the #FTE is your measure:
[FTE] = SUM( TableName[FTE] )
So then you'd create a measure:
[FTE future] = CALCULATE ( [FTE], DATESINPERIOD ( DimDate[Date] , 10, Year) )
You can change the parts in itallics, here's a list of Time Intelligence functions for inspriation:
Hi PBILearner2022 ,
Based on your description, I created these data.
1. create a date table to be used as a slicer.
DAX DateTable = ADDCOLUMNS ( CALENDAR(DATE(2024,1,1), DATE(2024,12,31)), // CALENDARAUTO(), "Year", YEAR([Date]), "Quarter", "Q" & FORMAT(CEILING(MONTH([Date])/3, 1), "#"), "Quarter No", CEILING(MONTH([Date])/3, 1), "Month No", MONTH([Date]), "Month Name", FORMAT([Date], "MMMM"), "Month Short Name", FORMAT([Date], "MMM"), "Month Short Name Plus Year", FORMAT([Date], "MMM,yy"), "DateSort", FORMAT([Date], "yyyyMMdd"), "Day Name", FORMAT([Date], "dddd"), "Details", FORMAT([Date], "dd-MMM-yyyy"), "Day Number", DAY ( [Date] ) )2. Create the measure.
Measure = IF(MAX('Table'[UtilizationDate]) >= EOMONTH(SELECTEDVALUE('DAX DateTable'[Date]),0) + 1, 1)3. Drag the measure into the filter pane of the table visual object to filter the data with the value of 1.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
