Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am looking for DAX that will count the number of Visits for the given time frame (today looking 7 days back). I have tried the below, but it is giving me an error regarding duplicate dates. Any ideas?
Requested Last 7 Days = CALCULATE ( COUNT ( 'Active Assignments'[assignment_seq] ), DATESINPERIOD ( 'Active Assignments'[date_requested], LASTDATE ( 'Active Assignments'[date_requested] ), -7, DAY ) )
Solved! Go to Solution.
@MrPowerBIPro, thanks. Below is the error:
Error Message: MdxScript(Model) (3, 9) Calculation error in measure 'Active Assignments'[Requested Last 7 Days]: A date column containing duplicate dates was specified in the call to function 'DATESINPERIOD'. This is not supported.
Again, I just wanted to count the number of Assignment IDs (assignment_seq) opened in the last 7 days (using the date_requested field).
EDIT: was overthinking it, found a solution:
Requested Last 7 Days = CALCULATE( DISTINCTCOUNT('Active Assignments'[assignment_seq]), FILTER(ALL('Active Assignments'), 'Active Assignments'[date_requested] >= TODAY()-7) )
Requested Last 7 Days = CALCULATE (
DISTINCTCOUNT ( survey_reports[responseId] ),
DATESINPERIOD ( date1[Date] ,LASTDATE(date1[Date] ), -7, DAY )
)
it's giving me a blank instead of value
There is some error in this method, I should get 0 instead I am getting 3......
week=CALCULATE(DISTINCTCOUNT(survey_reports[mobile]),DATESINPERIOD(survey_reports[date],LASTDATE(survey_reports[date]),-7,DAY))
@jsabo That's a Good news.
See this link to found more:
https://community.powerbi.com/t5/Desktop/DATESBETWEEN-Date-and-Time-Field/td-p/17462
Regards
Mostafa
Can you copy the error here?
@MrPowerBIPro, thanks. Below is the error:
Error Message: MdxScript(Model) (3, 9) Calculation error in measure 'Active Assignments'[Requested Last 7 Days]: A date column containing duplicate dates was specified in the call to function 'DATESINPERIOD'. This is not supported.
Again, I just wanted to count the number of Assignment IDs (assignment_seq) opened in the last 7 days (using the date_requested field).
EDIT: was overthinking it, found a solution:
Requested Last 7 Days = CALCULATE( DISTINCTCOUNT('Active Assignments'[assignment_seq]), FILTER(ALL('Active Assignments'), 'Active Assignments'[date_requested] >= TODAY()-7) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |