Problem with DATESINPERIOD function

[Anonymous]

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!

[AntrikshSharma]

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

[TomMartens · Accepted solution]

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

[amitchandak]

@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-La...

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


Appreciate your Kudos.

[Greg_Deckler]

@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