Missing dates visibility in a table/Visual

[Sankzpower]

Hi Guys

A very strange one for me. I have tried different solutions such as counting the days between from and to but none of seem it work. Below is my dataset. Ultimately, I would like Power bi to show visual visibility of missing days in each months.

Any help would be massively appreciated!

ID	Name	Value	DateFrom	DateTo
233	A	55.8	27/07/2018	25/08/2018
233	A	681.4	25/08/2018	25/09/2018
233	A	735.6	30/09/2018	25/10/2018
233	A	735.6	25/10/2018	25/11/2018

What I would like Power bi to show by months say last 12 months. 

ID	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18
233	31	28	31	30	31	30	27	0	4	0	6
233	Missing	Missing	Missing	Missing	Missing	Missing	Missing		Missing		Missing

[v-diye-msft]

Hi @Sankzpower 

if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

[lc_finance · Accepted solution]

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

[Sankzpower]

@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?

[lc_finance]

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

[Sankzpower]

@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 

[lc_finance]

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

[Sankzpower]

@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

[lc_finance]

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

[Sankzpower]

@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. 

[lc_finance]

Thank you @Sankzpower  for the kind words!

Your comment made my day

LC

[Sankzpower]

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