Start/End Date %Calculation by quarter

[Spazia]

Hello,

I have a calendar table and the following data:

CheckNbr	Key	Result	Start Date	End Date	Index	CheckNbr_Key
1	A	1	01/01/2020	31/05/2020	1	1A
2	A	0	01/01/2020	08/05/2020	2	2A
3	A	0	01/01/2020	24/02/2020	3	3A
4	A	0	01/01/2020	08/05/2020	4	4A
5	A	0	01/01/2020	31/12/9999	5	5A
6	A	1	01/01/2020	31/12/9999	6	6A
2	A	1	09/05/2020	31/12/9999	7	2A
3	A	1	25/02/2020	31/12/9999	8	3A
4	A	1	09/05/2020	31/12/9999	9	4A

I would like to calculate over time the nbr of 1 compare to the total of rows. I used this formula:

% Quality = 

VAR _FirstDate = FIRSTDATE ( 'Calendar'[Date] )

VAR _LastDate = LASTDATE ( 'Calendar'[Date] )

RETURN

CALCULATE (

 DISTINCTCOUNT ( Table[Index] ),

 Table[Start Date] <= _LastDate,

 Table[EndDate] >= _FirstDate,

 Table[Result]=1
)/

 CALCULATE (

 DISTINCTCOUNT ( Table[Index] ),

 Table[Start Date] <= _LastDate,

 Table[EndDate] >= _FirstDate 
 )

 This is working perfectly when in my chart in the x-Axis I have the date. But as soon as I want to put the quarter on the x-Axis the value aren't correct. 
If I put the Quarter in x-Axis I obtain: Quarter 1 = 42.86% and Quarter 2=62.50%

I'm expecting to have Quarter 1 = 50% and Quarter 2=83.3%. Because if a Check_nbr_Key appear twice in the same Quarter, only the one with the latest End Date should be kept.

In order to take into account that last parameter I tried this, but it didn't work:

VAR _FirstDate = FIRSTDATE ( 'Calendar'[Date] )

VAR _LastDate = LASTDATE ( 'Calendar'[Date] )

VAR MaxDate = CALCULATE (

 MAX ( Table[EndDate] ),

 Table[Start Date] <= _LastDate,

Table[EndDate] >= _FirstDate,


 )

RETURN

CALCULATE (

 DISTINCTCOUNT ( Table[Index] ),
 Table[Start Date] <= _LastDate,
 Table[EndDate] >= _FirstDate ,
 Table[Result]=1,
 Table[EndDate] = MaxDate
 )/

 CALCULATE (

 DISTINCTCOUNT ( Table[Index] ),

 Table[Start Date] <= _LastDate,
 Table[EndDate] >= _FirstDate,
 Table[EndDate] = MaxDate
)

Does anyone has an idea how I could, keep the one with the latest End Date?

I know that if I create a new table that calculates all the date between start and end date like in this post: Here

But doing that would be performance consuming, and this table will fastly reach millions of rows.

[amitchandak]

@Spazia ,

Refer, if this can help https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Spazia]

Hello @amitchandak ,

Unfortunatelly, what's explained in the link is very close to what I'm doing.

However, I found a work around by creating, in my calendar table, a column displaying the quarter only on the last date of the quarter. This avoid to have several rows for the same quarter and have confusion in the %calculation as it'll take the last date.