Forum Discussion

Sankzpower's avatar
Sankzpower
Helper I
6 years ago
Solved

Missing dates visibility in a table/Visual

Hi Guys   A very strange one for me. I have tried different solutions such as counting the days between from and to but none of seem it work. Below is my dataset. Ultimately, I would like Power bi ...
Need Help
Show and Tell
  • lc_finance's avatar
    lc_finance
    6 years ago

    Hi Sankzpower ,

     

     

    you can download my proposed solution from here.

     

    I did the following:

    1) create a new calendar table by ID.

    Date by ID = GENERATEALL(CALENDARAUTO(), VALUES('Days by ID'[ID]))

    2) Add to the calendar table a new column that checks if the date is included in the main table

    Included = 
var currentID = [ID]
var currentDate = [Date]
RETURN
COUNTX(FILTER('Days by ID','Days by ID'[ID]=currentID && 'Days by ID'[DateFrom]<=currentDate && 'Days by ID'[DateTo]>=currentDate),[Value] )

    3) Add one measure to count the missing days

    Count of missing days = COUNT([Date])-COUNTX('Date by ID',[Included])

    4) Add one measure that returns 'missing' if there are missing days

    Check if missing = IF([Count of missing days]<>0, "Missing")

     

    Below is a screenshot:

     

    I hope this helps you. Do not hesitate if you have further questions

     

    LC

    Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

lc_finance's avatar
lc_finance
Solution Sage
6 years ago

Hi Sankzpower ,

 

 

you can download my proposed solution from here.

 

I did the following:

1) create a new calendar table by ID.

Date by ID = GENERATEALL(CALENDARAUTO(), VALUES('Days by ID'[ID]))

2) Add to the calendar table a new column that checks if the date is included in the main table

Included = 
var currentID = [ID]
var currentDate = [Date]
RETURN
COUNTX(FILTER('Days by ID','Days by ID'[ID]=currentID && 'Days by ID'[DateFrom]<=currentDate && 'Days by ID'[DateTo]>=currentDate),[Value] )

3) Add one measure to count the missing days

Count of missing days = COUNT([Date])-COUNTX('Date by ID',[Included])

4) Add one measure that returns 'missing' if there are missing days

Check if missing = IF([Count of missing days]<>0, "Missing")

 

Below is a screenshot:

 

I hope this helps you. Do not hesitate if you have further questions

 

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

  • Sankzpower's avatar
    Sankzpower
    Helper I
    6 years ago

    lc_finance Thanks a lot much, exactly the logic which I am after. You are very detailed.

    In terms of scability, I have over 3000 IDs with a different from and to date. I see we can use 365 columns in terms of days and use Ids as rows and value present. is it possible that we can scale this same approach for over 3000s?

    • lc_finance's avatar
      lc_finance
      Solution Sage
      6 years ago

      Hi Sankzpower ,

       

      I am glad you like this approach.

      Regarding scalability, I think that the best way is to run this formula for the 3000 IDs and see if it works well.

      It's very possible that it will work fine.

       

      If you see that it's too slow, you can share with me a sample Power BI with the 3000 IDs and I can look at the formula and what optimization could be done.

       

      LC

      Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

      • Sankzpower's avatar
        Sankzpower
        Helper I
        6 years ago

        lc_finance Thanks for the reply. 

         

        I have attached the sampleLink here  file - Did not add 3 000 ids however added about 400 plus to test with. Your formula works perfectly but happy for you to take a look at it and suggest if any performance improvement can be made.

         

        Also, is there anyway the value field can be from each row combined into months? So potentially, the value will be split into daily from (from date and to date)? not sure if this can be achievable?

         

        Thanks in advance again