Count dates with no values

[Fapo]

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.

[v-yulgu-msft]

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

[AlB · Accepted solution]

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?

[Anonymous]

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

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(ISBLANK(CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Date]<EARLIER(Data[Date])))),BLANK(),1*(Data[Date]-CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Date]<EARLIER(Data[Date])))-1))

Hope this helps.

[Anonymous]

Thanks for the quick reply but it does't seem to be working.

Gap Days Calc = IF(ISBLANK(CALCULATE(MAX('Transaction Header History'[Business Day]),FILTER('Transaction Header History','Transaction Header History'[Business Day]<EARLIER('Transaction Header History'[Business Day])))),BLANK(),1*('Transaction Header History'[Business Day]-CALCULATE(MAX('Transaction Header History'[Business Day]),FILTER('Transaction Header History','Transaction Header History'[Business Day]<EARLIER('Transaction Header History'[Business Day])))-1))

It should be showing "1" or higher when there is a gap?

Any suggestions?

Thank you!

[Ashish_Mathur]

Hi,

Mine has to be written as a calculated column formula (not as a measure).

[Anonymous]

I thought I made a column.

Is this not the correct way?

[Ashish_Mathur]

No, it is not.  Click on the Data tab icon in the extreme right hand side vertical panel.  Click on the Table in which you want to write the formula and then click on New column.

[Anonymous]

Oh ok, that was what I had done in the first image, it is not a measure. I clicked on New Column and in the formula bar wrote the following: 

Gap Days Calc = IF(ISBLANK(CALCULATE(MAX('Transaction Header History'[Business Day]),FILTER('Transaction Header History','Transaction Header History'[Business Day]<EARLIER('Transaction Header History'[Business Day])))),BLANK(),1*('Transaction Header History'[Business Day]-CALCULATE(MAX('Transaction Header History'[Business Day]),FILTER('Transaction Header History','Transaction Header History'[Business Day]<EARLIER('Transaction Header History'[Business Day])))-1))

[Ashish_Mathur]

So did my solution work?

[Anonymous]

[Anonymous]

Hey,

Regrettably no. It just shows 0 even though I can see that there are gaps in the date from the previous row.  Not sure what to do now.

[Ashish_Mathur]

As you can see from the screenshot i posted, my calculated column formula works.  I do not know what mistake you are committing.  Share the link from where i can download your PBI file.

[Anonymous]

Yes you are right.

I even created the same table in Excel Power Pivot and it worked. But in Power BI it won't work for some reason. I'll get back to you again. Can I contact you privately? Can't really share client data with the link here.

Thank you again!

[Ashish_Mathur]

Share anonymised data.

[AlB]

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_ - 1

Please 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.

[Anonymous]

Hi,

Thanks for the reply!

I tried this solution as well but it didn't quite work. I added this column to the table:

Gap Days C = VAR previousDate_ = CALCULATE(MIN('Transaction Header History'[Business Day]),ALLEXCEPT('Transaction Header History','Transaction Header History'[Business Day])) return 'Transaction Header History'[Business Day] - previousDate_ - 1