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 the updated solution from here.
I added 2 columns in Days by ID:
- a column to calculate the number of days between DateFrom and DateTo
Days = DATEDIFF([DateFrom],[DateTo],DAY)+1- a column to calculate the Value per day, which is the value divided by the number of days
Days = DATEDIFF([DateFrom],[DateTo],DAY)+1
And I added one column in Date by ID, which uses the [Value per day] calculated previously
Value per day =
var currentID = [ID]
var currentDate = [Date]
RETURN
SUMX(FILTER('Days by ID','Days by ID'[ID]=currentID && 'Days by ID'[DateFrom]<=currentDate && 'Days by ID'[DateTo]>=currentDate),
[Value per day]
)
Let me know if this is what you are looking for!
Regards
LC
Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com
lc_finance Thanks a lot again. Its just simple thing but excuting is a big challenge as in the sense that I could do it excel but delivering via power bi is a big task. You absolutely nailed it.