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?

     

AlB's avatar
AlB
Community Champion
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?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

    • AlB's avatar
      AlB
      Community Champion
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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
         

         

         

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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!