How to do many to many comparison in Power BI with the help of measures and calculated columns

[shrafco1]

https://drive.google.com/file/d/14eGL2MvLIakMMoY0JZoBliPZldCSr3Oa/view?usp=sharing (PBIX file).

I have two dates column in my data set, Isolation Start Date & Isolation End Date. I have another column called Status which tells you wheather the indvidual get released or still in isolation. The problem is to count total number of people in isolation for each date based on isolation start date and isolation end date columns. I created calculated coulmn as follow

Isolation Time =

IF ( RELATED('Dim Date'[Date]) >= 'Isolation by Day'[Isolation Start Date] && RELATED('Dim Date'[Date]) <= 'Isolation by Day'[Isolation End Date], 1, 0 ).

Then I created a measure to sum up all the ones. Then, I put the measure and the date column in a column chart but the result is not what it required. I suppose to compare each isolation start date using  the formula above and save its result, which is comparing many dates in isolation start date column with other many dates in isolation end date. How to deal with this kind of comparison in Power BI. Takes each date and compare it using the formula and sum up the ones and save the result, then go to the next date do the same thing.  Thank you in advance

[shrafco1]

https://drive.google.com/file/d/14eGL2MvLIakMMoY0JZoBliPZldCSr3Oa/view?usp=sharing  (PBIX file)

@amitchandak This is the table and a sample of the calculations. I just used Total for April 21 calculated column as example so you can understand what I need to do, so for each range of Isolatin start date and Isolation end date check if contains April 21 put 1, then I sumed up all the ones, the total is 10. However, I wanted to do this calculation for each date in Isolation Start Date column. so I created a measure called Total # of isolation per day as you can see it in the screen shot below and I tested in a column chart  but it did not work correctly. You can see it below for example, the total number of isolation for April 21 in the card is 10 but when I used the measure Total # of isolation the result was only 5.  You can also check the PBIX file in the link above.

  

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[shrafco1]

Hi @Ashish_Mathur, thank you for the help, could you please explain what changes you made in Power query, I could not see it because of the error in the data. but I see the number of records are increased and no isolation start date and end date columns

Thank you  

[Ashish_Mathur · Accepted solution]

Please go through the steps in the Query Editor.

[amitchandak]

@shrafco1 ,Not very clear to me.

Refer if this can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.