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
lc_finance Ofcourse, the formula works great so I will ping it back if I face any further issues.
Apologises for the delay. Yes, I just read the email again and did not make it clear on the value. So for example, if the value is £150 with from date 01/02/2019 and to date 01/05/2019, then potentially need to calculate days value for Feb, Mar and Apr based on from and to date. attached link here For example:
| Id | Value | Datefrom | Dateto |
| 12 | 400 | 01/01/2019 | 31/03/2019 |
| 12 | 300 | 31/03/2019 | 01/06/2019 |
| 14 | 200 | 01/01/2019 | 31/03/2019 |
| 14 | 250 | 31/03/2019 | 01/06/2019 |
| 15 | 350 | 01/01/2019 | 31/03/2019 |
| 15 | 350 | 31/03/2019 | 01/06/2019 |
| 15 | 350 | 01/06/2019 | 01/08/2019 |
The visual in a simple table format:
| Id | Jan-19 | Feb-19 | Mar-19 | Apr-19 | May-19 | Jun-19 | Jul-19 | Aug-19 |
| 12 | 133 | 133 | 133 | 100 | 100 | 100 | ||
| 14 | 66.66 | 66.66 | 66.66 | 83.3 | 83.3 | 83.3 | ||
| 15 | 116.66 | 116.66 | 116.66 | 116.66 | 116.66 | 116.66 | 175 | 175 |
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.
Hi lc_finance
Sorry to bug again. I stuck with this and tried nearly some time but cant get it work. Again, its easier to put into excel and pivot the result what I need but would love to get this working in power bi.
I am looking for a measure to show total number of column count by id. I have exported the file output from power bi to excel and simply pivoted here Sample file . Just need the pivot table visual in power bi but again stuck.
Any help would be appreciated. Thank you