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 ,
I'm very glad it's working well!
For performance analysis, I'd need a bigger file where the formula is slow so I can check if alternative formulas are better.
However if the formula works well for you, I'd say to keep it.
Can you share more about 'the value field can be from each row can be combined into months'? If possible, with an example of the visualization?
Regards
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)+1And 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.