Forum Discussion
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 to show visual visibility of missing days in each months.
Any help would be massively appreciated!
| ID | Name | Value | DateFrom | DateTo |
| 233 | A | 55.8 | 27/07/2018 | 25/08/2018 |
| 233 | A | 681.4 | 25/08/2018 | 25/09/2018 |
| 233 | A | 735.6 | 30/09/2018 | 25/10/2018 |
| 233 | A | 735.6 | 25/10/2018 | 25/11/2018 |
What I would like Power bi to show by months say last 12 months.
| ID | Jan-18 | Feb-18 | Mar-18 | Apr-18 | May-18 | Jun-18 | Jul-18 | Aug-18 | Sep-18 | Oct-18 | Nov-18 |
| 233 | 31 | 28 | 31 | 30 | 31 | 30 | 27 | 0 | 4 | 0 | 6 |
| 233 | Missing | Missing | Missing | Missing | Missing | Missing | Missing | Missing | Missing |
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
11 Replies
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 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?
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
Hi Sankzpower
if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!
