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 have a table where I want to count the cell occurance over a period of time. I was able to accomplish an overall count by the measure:
Count = CALCULATE(COUNT('OpenRecords'[Name]))
but when I try to calculate for the past 30 days, I get an error. I was trying this and it was not working.
Solved! Go to Solution.
Easiest solution is to use Variables. You also need to define which field has the date stored in it. I've made some guesses
This would look like
Count = var filterDate = TODAY()-30
var output = CALCULATE(
COUNT('OpenRecords'[Name]),
'OpenRecords'[Date] >= filterDate
)
RETURN
output
The advantage of writing dax like the above is that you can test each step of your code if you ever need to bug fix.
Easiest solution is to use Variables. You also need to define which field has the date stored in it. I've made some guesses
This would look like
Count = var filterDate = TODAY()-30
var output = CALCULATE(
COUNT('OpenRecords'[Name]),
'OpenRecords'[Date] >= filterDate
)
RETURN
output
The advantage of writing dax like the above is that you can test each step of your code if you ever need to bug fix.
I should also add that this formula may not work in all contexts, because you might have additional filters which are constraining your data to less than the last 30 days. If thats the case you'd need to add in some ALL or related statements to expand the context. This requires thought as you need to understand how this will interact with your reports slicers and contexts. An example might be:
Count = var filterDate = TODAY()-30
var output = CALCULATE(
COUNT('OpenRecords'[Name]),
ALL('OpenRecords'),
'OpenRecords'[Date] >= filterDate
)
RETURN
output
(this would ignore any filters you've set that constrain the open records table).
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |