Forum Discussion
Missing dates visibility in a table/Visual
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
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?