Forum Discussion
Count dates with no values
Hello my friends:
I have created a Calendar table for my report. As such, it contains no date gaps.
On the other hand, I have a facts table. As its typical in facts tables, it contains transactions. It is not every day that a transaction occurs, so it does have date gaps.
My question is: is there a way to count the number of dates with no values? That is, the number of dates that exists on the calendar table, but does not exists on the facts table.
To give you a more concrete idea, I'll tell you more about the context:
We are an IT company. Some clients have monthly IT service contracts, but not every contract is used by the client every month (it is not every month that something malfunctions or breaks up!). So, we want to have a clear idea of how many months the contract was used vs how many months it was not used to eventually reach a KPI.
I've tried a calculated column along these lines:
if(isnotblank(sum(total_number_of_hours_consumed)))=True(), "USED CONTRACT", "UNUSED CONTRACT")
The idea is to create a USED/UNUSED slicer with this column. But the slicer only shows "USED CONTRACT", as if the unused contracts didn't exist.
Thank you very much in advance.
Hi there Fapo
Here's the general idea, where I'm calling your date table 'Date' and your fact table 'Sales':
With this you can get the number of dates that appear on the 'Sales' table:
A=COUNTROWS('Date'; Sales)
and with this the number of dates in the 'Date' table
B='COUNTROWS('Date')
then A-B would be the number of days that do not appear in 'Sales'. If you are familiar with the concept of expanded tables, that's what we are using in A. This assumes a relationship between 'Date' and 'Sales' of course.
From there you can apply slicers to detemine the period, client, etc.
Does that help?
17 Replies
Hi there Fapo
Here's the general idea, where I'm calling your date table 'Date' and your fact table 'Sales':
With this you can get the number of dates that appear on the 'Sales' table:
A=COUNTROWS('Date'; Sales)
and with this the number of dates in the 'Date' table
B='COUNTROWS('Date')
then A-B would be the number of days that do not appear in 'Sales'. If you are familiar with the concept of expanded tables, that's what we are using in A. This assumes a relationship between 'Date' and 'Sales' of course.
From there you can apply slicers to detemine the period, client, etc.
Does that help?
Thank you for this as this was a brilliant solution.
I'm having kind of the same issue, however, I need to be able to illustrate which days these, lets call them Gap Days, show up. Example is if a restaurant is closed on Christmas then it wouldn't have any sales on 2020-12-24.
For instance:
Order | Date | Gape Days
1 | 2020-12-22 | 0
2 | 2020-12-23 | 0
3 | 2020-12-25 | 1
4 | 2020-12-26 | 0
I hope I was clear in formulating my question.
Thanks in advance!
Maziar
Hi Anonymous
I don't quite understand the question. You mean you want a calculated column in your table counting how many days do not appear in the table between the current date and the immediately previous one (appearing in the table)? If so, create a calculated column:
Gap days = VAR previousDate_ = CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Date] ) ) RETURN Table1[Date] - previousDate_ - 1Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi Fapo,
My question is: is there a way to count the number of dates with no values? That is, the number of dates that exists on the calendar table, but does not exists on the facts table.
For this requirement, you can try AlB's suggestion.
CountDateswithNoValues =
COUNTROWS ( 'CalendarDate' ) - DISTINCTCOUNT ( 'Sales'[Date] )
We are an IT company. Some clients have monthly IT service contracts, but not every contract is used by the client every month (it is not every month that something malfunctions or breaks up!). So, we want to have a clear idea of how many months the contract was used vs how many months it was not used to eventually reach a KPI.
I've tried a calculated column along these lines:
if(isnotblank(sum(total_number_of_hours_consumed)))=True(), "USED CONTRACT", "UNUSED CONTRACT")
The idea is to create a USED/UNUSED slicer with this column. But the slicer only shows "USED CONTRACT", as if the unused contracts didn't exist.
Please illustrate the scenario with sample data and show us desired output. How to Get Your Question Answered Quickly
Best regards,
Yuliana Gu
