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

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.

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))
 
 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    So did my solution work?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hey,

     

    Regrettably no. It just shows 0 even though I can see that there are gaps in the date from the previous row.  Not sure what to do now.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    As you can see from the screenshot i posted, my calculated column formula works.  I do not know what mistake you are committing.  Share the link from where i can download your PBI file.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Yes you are right.

    I even created the same table in Excel Power Pivot and it worked. But in Power BI it won't work for some reason. I'll get back to you again. Can I contact you privately? Can't really share client data with the link here.

     

    Thank you again!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Share anonymised data.