Struggling to get result I want from TopN function

[masplin]

I have a datset that looks like this

SubmittedDateTime	ATEDate	SubmittedTime	Running total
17/11/2019 08:57	17/11/2019 00:00	1899-12-30 08:57:15	846
17/11/2019 09:00	17/11/2019 00:00	1899-12-30 09:00:16	847
17/11/2019 09:01	17/11/2019 00:00	1899-12-30 09:01:16	848
17/11/2019 11:14	17/11/2019 00:00	1899-12-30 11:14:26	849
17/11/2019 11:15	17/11/2019 00:00	1899-12-30 11:15:42	850
17/11/2019 11:20	17/11/2019 00:00	1899-12-30 11:20:18	851
17/11/2019 12:01	17/11/2019 00:00	1899-12-30 12:01:34	852
17/11/2019 12:25	17/11/2019 00:00	1899-12-30 12:25:57	853
17/11/2019 15:05	17/11/2019 00:00	1899-12-30 15:05:25	854
17/11/2019 15:58	17/11/2019 00:00	1899-12-30 15:58:09	855
17/11/2019 16:25	17/11/2019 00:00	1899-12-30 16:25:39	856
17/11/2019 16:54	17/11/2019 00:00	1899-12-30 16:54:05	857
17/11/2019 16:59	17/11/2019 00:00	1899-12-30 16:59:25	858
17/11/2019 17:16	17/11/2019 00:00	1899-12-30 17:16:04	859
17/11/2019 17:33	17/11/2019 00:00	1899-12-30 17:33:49	860
17/11/2019 17:47	17/11/2019 00:00	1899-12-30 17:47:07	861
17/11/2019 17:57	17/11/2019 00:00	1899-12-30 17:57:14	862
17/11/2019 19:29	17/11/2019 00:00	1899-12-30 19:29:20	863
17/11/2019 19:57	17/11/2019 00:00	1899-12-30 19:57:18	864
17/11/2019 20:38	17/11/2019 00:00	1899-12-30 20:38:43	865
17/11/2019 20:39	17/11/2019 00:00	1899-12-30 20:39:32	866
17/11/2019 21:12	17/11/2019 00:00	1899-12-30 21:12:15	867
17/11/2019 21:29	17/11/2019 00:00	1899-12-30 21:29:36	868
17/11/2019 21:30	17/11/2019 00:00	1899-12-30 21:30:24	869
17/11/2019 21:31	17/11/2019 00:00	1899-12-30 21:31:18	870
17/11/2019 21:58	17/11/2019 00:00	1899-12-30 21:58:28	871
17/11/2019 22:15	17/11/2019 00:00	1899-12-30 22:15:15	872
17/11/2019 23:18	17/11/2019 00:00	1899-12-30 23:18:58	873
17/11/2019 23:40	17/11/2019 00:00	1899-12-30 23:40:54	874

I want to be able to pickout what the rolling total was on each hour of each day.

I have a Date table and also a Time table

I wrote this measure

Measure 3 = 
VAR
MaxTime=MAX('Time'[Hour])
VAR
MaxDay=Max('Date'[Date])
RETURN
CALCULATE(VALUES(ATEs[Running total]),
                                   TOPN(1,
                                          CALCULATETABLE(ATEs,
                                                        ALL(ATEs[SubmittedTime]) ,
                                                        ATEs[SubmittedTime]<=MaxTime ,
                                                              ATEs[ATEDate]=MaxDay
                                                        ),
                                         ATEs[SubmittedDateTime],
                                         DESC
                                        )
                         )

and put it in this matrix with Date[Day] on the rows and Time[Hour] on the columns

So i have 2 issues that i can't work out

1. The number for 17/11 12 hours is the value at 12:25, but I was expecting I had created a table with only submitted time up to 12 and then taken the top row. Clearly this isnt happening and is taking submitted time up to where the hour is 12.  I tried wrapping the MaxTime in TIME(MaxTime,0,0) to force it to 12:00:00, but just blows up. given Time[Hour] is a number I'm not sure how it is doing the comparison with submittedtime which is a time. 

2. I have a blank for 10 because there are no records in the 10-11 bucket on 17/11. I tried using the ALL(Submitted time) because i can see the 10 on the column is creating a context that excludes the times before 10, but deons't seem to make any difference. The number here should be 848 the value at 9:02 as last value before 10:00:00. 

Appreciate any assitance

Mike

[mwegener]

Hi @masplin 

check this out.

PBIX

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[masplin]

Hi. I wish I could understand how this has managed to fill in the blanks!!!

  I assume ALLSELECTED(Time[time]) selects all the hours so removing column context?  I can't work out the syntax of the ISONORAFTER. Is Time[Time] the time each row in the data and Max(Time[time]) is the limit of the column its in i.e 10:59 for column 10? If so really don't understand as definition is "If the sort order is descending, the comparison to be done is second parameter less than or equal to first parameter."  So that would be true for rows in the date with a time after the hour in the column? ISONORAFTER with DESC the same as ISONORBEFORE?

I still have the same issue that the data is in the wrong columns. At 9am 847 are outstanding not 848 , at 11 848 are outstanding not 851. Actually cracked it need to use MIN(Time[Time]) so uses 10:00 in thr 10 bucket.

Thanks  for your help

Mike

[mwegener]

Hi @masplin ,

i updated the file.

Now every started hour is displayed in the next hour.

The "Running total" calculation is the standard Power BI  Quick measure.

DAX does not knows a ISONORBEFORE.

But it is important that your Measure is aggregatable. (changes)

PBIX

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[masplin]

Ah I see the problem. I accidentally gave you a slice of data where the running total looks continuous.  The actual function behind this is not continuous so the next day looks like this.  Its a measure of incoming queries minus any ones dealt with so figure can go down. So a running total isn't possible. Henmce reason i was using TOPN

However the ALLSELECTED clause and ISONORAFTER are new to me so maybe will help me get the answer i need so appreciate your help

[mwegener · Accepted solution]

Hi @masplin ,

the data should not be a problem, it just needs to be transformed.

You find the necessary steps in the PBIX

https://dax.guide/allselected/

https://dax.guide/isonorafter/

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi @masplin,

did you solve your problem?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[masplin]

Yes but using the TOPN version. your tip on ALLSELECTED and ONORBEFORE reallyhelped me solve it.

Sadly it turned out to be a completely pointless less calculation so I deleted it!

Mike 

[mwegener]

Hi @masplin,

then please mark one post as solution, this will help others to get this informations.