topic Problem with DATESINPERIOD function in DAX Commands and Tips

Problem with DATESINPERIOD function

Anonymous — Thu, 27 Aug 2020 10:55:42 GMT

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

Reports_Lst2Wks_ALT = CALCULATE(DISTINCTCOUNT(selfreports[AccountName]),DATESBETWEEN(selfreports[Date],DATE(2020,08,14),DATE(2020,08,27)))

However I need the function to be adaptive to the current date.

I would expect the following function to give the same result as above:

Reports_Lst2Wks = CALCULATE(DISTINCTCOUNT(selfreports[AccountName]),DATESINPERIOD(selfreports[Date],[TODAY](),-7,DAY))

But instead it gives a result of 0.

Can anyone see what the problem might be?

Thanks!

Re: Problem with DATESINPERIOD function

Greg_Deckler — Thu, 27 Aug 2020 11:05:47 GMT

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

Re: Problem with DATESINPERIOD function

amitchandak — Thu, 27 Aug 2020 11:06:51 GMT

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/1051123

or 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.

Re: Problem with DATESINPERIOD function

TomMartens — Thu, 27 Aug 2020 11:14:47 GMT

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

Re: Problem with DATESINPERIOD function

AntrikshSharma — Thu, 27 Aug 2020 12:06:48 GMT

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 ) )