Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

 

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!
1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
AntrikshSharma
Super User
Super User

@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
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
amitchandak
Super User
Super User

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

Greg_Deckler
Super User
Super User

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.