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?

     

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!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I thought I made a column.

       

       

      Is this not the correct way?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        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.