The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi everyone,
I have a date slicer for selecting From and To dates from the Calendar Table. The data shows accordingly.
I want to set the From and To dates on the slicer dynamically according to the current day. From date should start from 25th of the month and To date should end with 24th of the following month.
Criteria: If the current day equals or after the 25th of the current month then From date should be set to 25th of that month and To date to be set to 24th of the following month.
Examples:
Let's say today is 17th of April. Then the From date will be 25.02.2023 and To date will be 24.03.2023
Let's say today is 25th of April. Then the From date will be 25.03.2023 and To date will be 24.04.2023
Let's say today is 1st of May. Then the From date will be 25.03.2023 and To date will be 24.04.2023
I hope any some can help me with how to set the From and To dates on the slider dynamically.
Solved! Go to Solution.
Hi @ozgeozkaya
Please refer to attached sample file amended as required.
Use the following measure in the three visuals
Sum of Transaction Period =
SUMX (
FILTER (
VALUES ( 'Date'[Date] ),
VAR CurrentDay = DAY ( TODAY ( ) )
VAR MonthOffset = IF ( CurrentDay < 25, - 1 )
VAR StartEOMonth = EOMONTH ( TODAY ( ), MonthOffset - 1 )
VAR EndEOMonth = EOMONTH ( TODAY ( ), MonthOffset )
VAR StartMonth = MONTH ( StartEOMonth )
VAR EndMonth = MONTH ( EndEOMonth )
VAR StartYear = YEAR ( StartEOMonth )
VAR EndYear = YEAR ( EndEOMonth )
VAR StartDate = DATE ( StartYear, StartMonth, 25 )
VAR EndDate = DATE ( EndYear, EndMonth, 24 )
RETURN
'Date'[Date] >= StartDate
&& 'Date'[Date] <= EndDate
),
CALCULATE ( SUM ( Overtime[Transaction Period] ) )
)
Dear @tamerj1 Thank you for the prompt reply. It is working with one glitch. According to the today's date criteria, the slicer should be 25.02.2023 - 24.03.2023 not 25.03.2023 - 24.04.2023 because today is not yet 25th of the month. Once today is 25th or greater than 25th of the current month (April), then it will display as 25.03.2023 - 24.04.2023.
Examples:
Let's say today is 18th of April. Then the From date will be 25.02.2023 and To date will be 24.03.2023
Let's say today is 25th of April. Then the From date will be 25.03.2023 and To date will be 24.04.2023
Let's say today is 1st of May. Then the From date will be 25.03.2023 and To date will be 24.04.2023
@ozgeozkaya
Yes that was a mistake. Please refer to the updated solution in my original reply.
Dear @tamerj1, the date slicer shows the correct dates now however for some reason it is not filtering the data. I am not sure if this is a bug or not but when you manually modify the slicer and choose the dates it filters the data accordingly but when you leave it as it is in the slicer filter you created, it is not filtering the data. It shows all available data in the table.
This is a visual level filter. You need to add it to every visual that needx to be filtered this way.
@tamerj1 Thank you for your reply. I reattached your file. As you may see only the table visual which has date on it is filtered but the other visuals (Card and Column chart) are not able to filter the data correctly after the visual level filter is applied.
Hi @ozgeozkaya
Please refer to attached sample fie. Create the following filter measure, place in the filter pane of the slicer, select "is not blank" then apply the filter.
DateFilter =
VAR CurrentDay = DAY ( TODAY ( ) )
VAR MonthOffset = IF ( CurrentDay < 25, - 1 )
VAR StartEOMonth = EOMONTH ( TODAY ( ), MonthOffset - 1 )
VAR EndEOMonth = EOMONTH ( TODAY ( ), MonthOffset )
VAR StartMonth = MONTH ( StartEOMonth )
VAR EndMonth = MONTH ( EndEOMonth )
VAR StartYear = YEAR ( StartEOMonth )
VAR EndYear = YEAR ( EndEOMonth )
VAR StartDate = DATE ( StartYear, StartMonth, 25 )
VAR EndDate = DATE ( EndYear, EndMonth, 24 )
VAR Result =
COUNTROWS (
FILTER (
'Date',
'Date'[Date] >= StartDate
&& 'Date'[Date] <= EndDate
)
)
RETURN
Result
Yes this method does not work with card visuals. The chart seems to be filtered but not to the desired values. I'll let you know if I was able to find a workaround for that. Very busy today so probably won't reply to you soon meanwhile please share the sample file with visuals via wetransfer or dropbox link.
Hi @ozgeozkaya
Please refer to attached sample file amended as required.
Use the following measure in the three visuals
Sum of Transaction Period =
SUMX (
FILTER (
VALUES ( 'Date'[Date] ),
VAR CurrentDay = DAY ( TODAY ( ) )
VAR MonthOffset = IF ( CurrentDay < 25, - 1 )
VAR StartEOMonth = EOMONTH ( TODAY ( ), MonthOffset - 1 )
VAR EndEOMonth = EOMONTH ( TODAY ( ), MonthOffset )
VAR StartMonth = MONTH ( StartEOMonth )
VAR EndMonth = MONTH ( EndEOMonth )
VAR StartYear = YEAR ( StartEOMonth )
VAR EndYear = YEAR ( EndEOMonth )
VAR StartDate = DATE ( StartYear, StartMonth, 25 )
VAR EndDate = DATE ( EndYear, EndMonth, 24 )
RETURN
'Date'[Date] >= StartDate
&& 'Date'[Date] <= EndDate
),
CALCULATE ( SUM ( Overtime[Transaction Period] ) )
)
@tamerj1 wonderful solution. I will keep an eye on it on 25.04.2023 for final confirmation if you don't mind. Thank you again.
User | Count |
---|---|
11 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
22 | |
14 | |
14 | |
9 | |
7 |