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 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
lc_finance Thanks for the reply.
I have attached the sampleLink here file - Did not add 3 000 ids however added about 400 plus to test with. Your formula works perfectly but happy for you to take a look at it and suggest if any performance improvement can be made.
Also, is there anyway the value field can be from each row combined into months? So potentially, the value will be split into daily from (from date and to date)? not sure if this can be achievable?
Thanks in advance again
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