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 want to create a measure to calculate the number of records that were active on a specific date.
I came up with a measure like this:
NumberOfRecordsActiveOnDate = CALCULATE(DISTINCTCOUNT(mytable[ID]), FILTER(mytable, AND(mytable[createdon] <= RELATED(mydatetable[date]), mytable[deletedon] < RELATED(mydatetable[date])))
However, this does not return anything when I filter on a specific date...
any ideas?
thanks,
Koen
Solved! Go to Solution.
I just came accross the following post:
this will probably lead me in the right direction...
Well, if you understand how you need to filter an amount between data.
You will have two possibilities.
1- use the DATESBETWEEN function in filter
Example:
I just came accross the following post:
this will probably lead me in the right direction...
@marceloVVR should I have relations between the datetable and both datefields in my table?
like:
- datetable[date] 1----* mytable[startdate]
- datetable[date] 1----* mytable[enddate]
or doesn't that matter?
hi @KJanssens
For your case, it is a startdate and enddate problem, Very common. See this post about it:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
you don't need to create create two relationship with date table, just create a measure as this blog and use date field from calendar.
If you still have the problem, please share some sample date and your expected output.
Regards,
Lin
It doesn't matter, being data of the same type and use the FILTER before, as in the example I sent
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |