cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Fapo Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count dates with no values

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?

 

2 REPLIES 2
Super User
Super User

Re: Count dates with no values

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?

 

v-yulgu-msft Super Contributor
Super Contributor

Re: Count dates with no values

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 145 members 1,670 guests
Please welcome our newest community members: