Forum Discussion

Fapo's avatar
Fapo
Frequent Visitor
7 years ago
Solved

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 transacti...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

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