Quarter calculation help

[Anonymous]

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

[Greg_Deckler]

For the first part, you should be able to get there using EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

For the second part when you exclude it from the quarter, I'm not clear on why it gets excluded.

[Anonymous]

@Greg_Deckler - Basically the 2nd part means the following -  

As you must have seen in the table i have sent, the End passage of the bus is not completed in Q2 , so it should be the part of Q3 calculation. However this data is updated on day to day basis.

We have to also exclude the records whose end passage data is not present in the current days file. i.e. a scenario wherein Bus started on dec 31st 2018 but its end passage is on 1st feb 2019 but as of today the records that i have is only till 24/01/2019. So all the entries from 31st dec 2018 till today should not be part of any calculation of hrs for Q1 2019. Where as there might be other bus whose end passage is completed within the specified time frame and its hrs sum should be a part of Q1 2019.

Just one more information the data that we get is incremental, i.e. on daily basis we get the delta load.

[AlB · Accepted solution]

Hi @Anonymous

I would create two additional calculated columns in your table, one with the actual year and one with the actual quarter in which the hours will be counted. Once this is done you can use these columns to slice instead of the Year and Quarter columns you have now.

Do note that the code looks for the corresponding "End  passage" row for each fragment and will return a blank when it does not find it. This will happen in the rows of the last passage in your sample data that start in Q2 but have no end. If you want a different behavior the code would have to be modified. It also assumes that "Halt"  is the only event that can appear in the table outside a passage and that the hours of "Halt" have to be counted in whatever in the actual quarter the "Halt" takes place.  Here are the columns:

Quarter4HoursCount = 
IF (
    Table1[Stop Point] = "Halt";
    Table1[Quarter];
    VAR _RowWithEndPassageDate =
        TOPN (
            1;
            CALCULATETABLE (
                Table1;
                Table1[Date] >= EARLIER ( Table1[Date] );
                Table1[Stop Point] = "End passage";
                ALLEXCEPT ( Table1; Table1[Bus Name] )
            );
            Table1[Date]; ASC
        )
    RETURN
        CONCATENATEX ( _RowWithEndPassageDate; [Quarter] )
)

Year4HoursCount = 
IF (
    Table1[Stop Point] = "Halt";
    Table1[Year];
    VAR _RowWithEndPassageDate =
        TOPN (
            1;
            CALCULATETABLE (
                Table1;
                Table1[Date] >= EARLIER ( Table1[Date] );
                Table1[Stop Point] = "End passage";
                ALLEXCEPT ( Table1; Table1[Bus Name] )
            );
            Table1[Date]; ASC
        )
    RETURN
        SUMX ( _RowWithEndPassageDate; [Year] )
)

Code formatted with  

[Anonymous]

@AlB - Thanks alot for the guidance , you have covered the exact scenario which i would require. However just would like to know if at all a bus starts in Dec 2018 i.e. Q4 and ends in 31st Jan 2019 Q1 , would the logic assign the Q1 against such records and if the particular bus has not recahed its end passage what values would be populated in the new quarter and year column for that particular bus records. 

And I believe once on day to day basis i will get additional records for that bus passage and eventually would get the end passage data for the same on 31st Jan 2019, the code will pick up the fragment and would update it to Q1 and year 2019?

[AlB]

@Anonymous

Yes, if you look at the code, what it does is look for the next "End passage" to which each line belongs and it will assign the year and and quarter in which the "End passage" takes place. So it should work in your example. It is because of cases like this that I included a new column for the year too, since both would change.

Like I said, the  code will return a blank if it doesn't find the corresponding "End passage". That could be modified but i think it's the most consistent behavior. You can then see the integrity of your data as well. If you refresh the data to include that missing "End passage", the column will be recalculated and will populate those blank rows

But hey, try it out and see for yourself.     

[Anonymous]

@AlB - The code worked, can you help me with how should i troubleshoot the dax written by you basically i want to get into how each of the query is working