Sum calculation help

[Anonymous]

Hi All,

I am stuck with a measure calculation , scenario is as below - 

  

So basically I need to calculate the sum of Hrs considering only the records highlighted in Bold i.e. Take an example of Q2 , include the records that have their end passage in the same quarter but started in previous quarter and exclude the records which start in the same quarter but dont end in the same quarter. So based on the quarter and year what user selects the sum needs to be calculated for the stoppages that end in the same quarter.

[v-piga-msft]

Hi @Anonymous,

I'm still a little confused about your logic.

Based on your information, do you want to calculate the sum of the Hrs which has been highlight in yellow?

If it is convenient, could you share your data sample as table format and your desired output so that I can have a test and get the solution.

Best Regards,

Cherry

[Anonymous]

@v-piga-msft -

Below is the test data

Year	Quarter	Bus Name	Stop Point	Date	Hrs
2018	Q1	Amber	Start Passage	15/03/18	123
2018	Q1	Amber	a	16/03/18	12
2018	Q1	Amber	End passage	20/03/18	14
2018	Q1	Amber	Halt	25/03/18	32
2018	Q1	Amber	Start Passage	27/03/18	23
2018	Q1	Amber	e	28/03/18	56
2018	Q2	Amber	f	02/04/18	67
2018	Q2	Amber	g	03/04/18	89
2018	Q2	Amber	End passage	04/04/18	1
2018	Q2	Amber	Halt	05/04/18	14
2018	Q2	Amber	Start Passage	06/06/18	25
2018	Q2	Amber	a	22/06/18	34
2018	Q2	Amber	End passage	24/06/18	31
2018	Q2	Amber	Start Passage	27/06/18	76
2018	Q2	Amber	a	28/06/18	85
2018	Q3	Amber	b	14/07/18	64
2018	Q3	Amber	c	18/07/18	54
2018	Q3	Amber	d	22/07/18	51
2018	Q3	Amber	e	27/07/18	35

Basically the requirement is to calculate the total hrs the records which needs to be included in the calculation is with below condition. Let us assume that user selects quarter 2 as a slicer. Now the while calcuating the sum for quarter 2 , measure should include the records as follows - 

Include - the hours wherein bus might have been started in previous quarter but have the end passage in the current quarter i.e. If you see the above table 1st two records highlighted in orange are part of Q1 but as the bus end passage is in Q2 , these values should be part of Q3 hrs

Accordingly - consider the entry dated 27/06/2018 onwards wherein the bus has started in Q2 , but it should be excluded from the calculation of Hrs for Quarter for that particular Bus.

So if you go through the above statements and compare it with the values that i have highlighted you would be able to link them together.

Thanks once again for the kind help

[Anonymous]

In my previous reply i had by mistake written Q3 to corrected to Q2

@v-piga-msft -

Below is the test data

Year	Quarter	Bus Name	Stop Point	Date	Hrs
2018	Q1	Amber	Start Passage	15/03/18	123
2018	Q1	Amber	a	16/03/18	12
2018	Q1	Amber	End passage	20/03/18	14
2018	Q1	Amber	Halt	25/03/18	32
2018	Q1	Amber	Start Passage	27/03/18	23
2018	Q1	Amber	e	28/03/18	56
2018	Q2	Amber	f	02/04/18	67
2018	Q2	Amber	g	03/04/18	89
2018	Q2	Amber	End passage	04/04/18	1
2018	Q2	Amber	Halt	05/04/18	14
2018	Q2	Amber	Start Passage	06/06/18	25
2018	Q2	Amber	a	22/06/18	34
2018	Q2	Amber	End passage	24/06/18	31
2018	Q2	Amber	Start Passage	27/06/18	76
2018	Q2	Amber	a	28/06/18	85
2018	Q3	Amber	b	14/07/18	64
2018	Q3	Amber	c	18/07/18	54
2018	Q3	Amber	d	22/07/18	51
2018	Q3	Amber	e	27/07/18	35

Basically the requirement is to calculate the total hrs the records which needs to be included in the calculation is with below condition. Let us assume that user selects quarter 2 as a slicer. Now the while calcuating the sum for quarter 2 , measure should include the records as follows - 

Include - the hours wherein bus might have been started in previous quarter but have the end passage in the current quarter i.e. If you see the above table 1st two records highlighted in orange are part of Q1 but as the bus end passage is in Q2 , these values should be part of Q2 hrs

Accordingly - consider the entry dated 27/06/2018 onwards wherein the bus has started in Q2 , but it should be excluded from the calculation of Hrs for Quarter for that particular Bus.

So if you go through the above statements and compare it with the values that i have highlighted you would be able to link them together.

Thanks once again for the kind help

[Anonymous]

@v-piga-msft -  were you able to find some solution for the below mentioned issue