DAX query for the below request

[Anonymous]

Hello community,

My data is something like above where a company belongs to different Sides based on the projects. Every project has a start date and project active date.

I want to calculate the number of sides for a company between start date and project active date and based on that want to calculate how many companies at a particular time between start date and project active date belong to which category.

Category based on number of sides ::: count (1) = burger, count(2)= burger+fries, count(3)=happy meal 

For example,

Mcdonalds has 3 sides : MS, PS and SD and 3 projects : A, B and C.

Mcdonalds will be active from 2017-08-11 to 2022-08-01

Now, from (startdate)2017-08-11 to 2018-01-09 it had one side MS project A so Burger category and from 2018-01-10 one more project C was added so Burger+fries till 2019-08-01 when project B was added so now its an happy meal but now we also have to include project active date of projects while counting the number of sides.

All projects are active but on 2021-01-10 we loose 1 so now it is 2=burger+fries again on 2021-08-11 we loose one more = burger and this burger will stay active till 2022-08-01.

This is just one client, MCD but in one visual I have to show for all clients at a particular time how many companies are in which category.

Something like below:

I have also attached the pbix file with the message.

Appreciate your help.

Thank you

[AlexisOlson · Accepted solution]

Like this?

To get this, I converted the date columns from text to date data type, created a new calculated table DimDate using CALENDARAUTO() and defined the following measure:

CountSides =
VAR SelectedDate = MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sample'[Sides] ),
        'Sample'[EndDate] >= SelectedDate,
        'Sample'[StartDate] < SelectedDate
    )

[Anonymous]

Thanks Alexis 😊 You are awesome. Solution worked for me

[Anonymous]

This helps but what I exactly want is a measure based on count of sides : 1(Burger),2(burger+fries),3(happy meal) and 4(happy meal+nuggets) and then show the count of companies that belong to different category at a time

[AlexisOlson]

Like this?

For this, I created a new table Sides with a single column Count with values 1 through 4 and a new measure

CompanyCount = 
VAR SideCount = SELECTEDVALUE ( Sides[Count] )
RETURN
    SUMX ( VALUES ( 'Sample'[Company] ), IF ( [CountSides] = SideCount, 1 ) )

[smpa01]

@Anonymous  please attache the pbix

[Anonymous]

https://proserveit-my.sharepoint.com/:u:/p/jpatel/ET5kzKlubB1Js4tFtFSEcVEBSVyYB3opdFy3MBckJzMVRQ?e=p... 

[smpa01]

@Anonymous  doesn't work

[Anonymous]

Try again: https://proserveit-my.sharepoint.com/:u:/p/jpatel/ET5kzKlubB1Js4tFtFSEcVEBBx30uMnmHp1V1PYFvUmH5w?e=m...