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?

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Ashish_Mathur
Super User
5 years ago

So did my solution work?