Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
@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 )
)
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 , 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.
@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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
14 | |
13 | |
12 | |
9 |