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

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.