Forum Discussion
Problem with DATESINPERIOD function
Hello,
I have a table of self reports of COVID symptoms which contatins user ID (AccountName) and the date of the report.
I'm trying to create a function which sums the unique reports over the last two weeks.
When I enter the dates manually using DATESBETWEEN I get the expected result
Hey Anonymous ,
you can use this DAX statement instead:
Reports_Lst2Wks_ALT = var _today = TODAY() var _7DaysBeforeToday = _today -7 return CALCULATE( DISTINCTCOUNT(selfreports[AccountName]) ,DATESBETWEEN(selfreports[Date] , _7DaysBeforeToday , _today) )DATESINPERIOD requires a dedicated date table without gaps, for this I recommend following the link Greg_Deckler provided in his post.
Hopefully, this helps to tackle your challenge.
Regards,
Tom
4 Replies
Hey Anonymous ,
you can use this DAX statement instead:
Reports_Lst2Wks_ALT = var _today = TODAY() var _7DaysBeforeToday = _today -7 return CALCULATE( DISTINCTCOUNT(selfreports[AccountName]) ,DATESBETWEEN(selfreports[Date] , _7DaysBeforeToday , _today) )DATESINPERIOD requires a dedicated date table without gaps, for this I recommend following the link Greg_Deckler provided in his post.
Hopefully, this helps to tackle your challenge.
Regards,
Tom
Anonymous - See if this helps you better understand DATESINPERIOD - https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-DATEADD/m-p/1259467#M583
Anonymous , not very clear , refer if this can help
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123or measures like, with date calendar
Rolling 14day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-14,Day))
Rolling 14day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-14,Day))
Rolling 14day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Today(),-14,Day))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Anonymous Are you not slicing data by dates table?
= VAR MaxVisibleDate = MAX ( Dates[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( selfreports[AccountName] ), DATESINPERIOD ( Dates[Date], MaxVisibleDate, -7, DAY ) )
