Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have locationIDs, I am trying to count the total number of distinct days in date range that the Locations occur and divide by the total date range selected.
IE If I filter August 2018, I should see 31 days total but I only see 25 because those are the only dates in my data set.
count(TOTAL DISTINCT [Start Date])
So i tried this
((max(total [Start Date])-min( total [Start Date]))+1)
Now here is the kicker.
I want to select any or all day of week in a filter pain so I created this.
((((max(total [Start Date])-min( total [Start Date]))+1)/7*(count(distinct [Day of Week])))
This should give me 23, but it gives me 22.14
How can i get the distinct dates for a date range regardless of my Location IDs date occurrence, but yet filter my Location ID counts by date range?
Wrapping
((((max(total [Start Date])-min( total [Start Date]))+1)/7*(count(distinct [Day of Week])))
in ceil()-function gives 23 instead of 22.14
This works, however if there is no data for a date that date is not counted...I would like to still have the total days in the date range.