Use a Gauge visual to count dates older than...

[Skotzmun]

I am completely new to Power BI and - if I am honest - finding it maddenly frustrating, but I have no option but to persevere. I am currently only working at personal "Desktop" level and trying to create a simple KPI measurement that has stumped me after several hours of search engine querying.

I have several hundred devices that check into a central system on a regular basis. From that central system I can pull a report into Excel sheet for all "Players" that includes a column containing "Last Connected DateTime".

I want to use a Gauge to show how many players have connected in the last two days. The MAX would be all players (counting "PlayerID") and the Value would be the count of all players connected within 48 hours of a given date (FWIW, I created a Measure called dateLatest that contains the most recent value in the "Last Connected DateTime" column to try and use for this).

This seems like it should be a simple ask, but I have completely failed and cannot figure it out at all. I can even count the number of dates, never mind count only those newer than dateLatest. 

I can usually google-phrase well enough to find stuff like this, but not for this. My only option is to start posting on forums and hope I can find someone able to steer me away from an increasingly attractive drinks cabinet. 😕

[v-robertq-msft · Accepted solution]

Hi, @Skotzmun 

Sorry for keeping you waiting so long. 😔

According to your description and sample pbix file, I think you can try this measure to calculate the count you wanted:

Connected within 2 days of dateLatest =

var _dateLatest=MAXX(ALL(Players),[Last Check In Time])

return

COUNTX(FILTER(ALL(Players),[Last Check In Time]<=_dateLatest&&[Last Check In Time]>=_dateLatest-2),[FaceID])

Then place the measure into the “Value” field of the gauge chart, you can get what you want, like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Skotzmun]

Hi Robert - that's exactly what I was looking for - thank you!

It's considerably more efficient than the "extra columns and multiple measures" hash-up I had to do to get round it. 🙂

If possible can I ask a clarification (I am completely new to DAX and unfortunately am one of those people who does not pick up new code variations quickly).

What is the purpose of the return statement between defining the variable and initiating COUNTX? Is that required to make the variable _dateLatest available for the subsequent COUNTX expression?

If so, what is the purpose of that having to be explicitly stated in DAX?

But many, many thanks for your assistance with that count. 

KR

Skotzmun

[v-robertq-msft]

Hi, @Skotzmun 

As far as I’m concerned, the “Return” is used to return the output of this DAX statement. If you used the “VAR” in this DAX statement, you have to use the “Return” to declare what is the output of this DAX statement. The _dateLatest is the variable I defined, which is used in the Countx() function I used.

More info about the VAR

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Skotzmun , Not very clear . You need a measure like

New measure =
var _max = maxx(allselected(Table), Table[Date])
var _max = _max -2
return
calculate(count(Table[Player]), filter(Table, Table[Date] >=Min && Table[Date]<=_min))

Or like

New measure =
var _max = maxx(allselected(Table), Table[Date])
var _max = _max -2
return
calculate(count(Table[Player]), filter(all(Table), Table[Date] >=Min && Table[Date]<=_min))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Skotzmun]

Hi, thanks for the response. I didn't think it would make sense to anyone else. 🙂

I have created a simplified pbix that contains the relevant anonymised data and an indication of what I am trying to achieve. I have also included my measure dateLatest in case it is of use (as an aside, I can't even figure how to create a Measure that is basically (dateLatest - 2 days), so I'm really feeling like a dunce here).

(Note, the data includes an "online" field but that's not ideal here as that is at the point the report was generated and is refreshed every 10 minutes. I am after longer-term offline issues).

FWIW, This is a WeTransfer link to get the files, or is there a better way to share such things here? For future reference, do shared users just need the pbix (60KB) or is the underlying Excel (38KB) also useful?