Percent completed within five years gauge

[ShameDonkey]

I am trying to find a measure to base a gauge result on of a column with dates. Need it to be of the total number of dates within five years. Basically, if the date is within five years the result is compliance. Trying to build a gauge to do so. Any ideas?

 

[JustynaMK]

Hi,

Not quite sure what you're trying to achieve - can you provide some further description or some screenshots, please?

Do you mean to present somewhere one date, and if that date falls into a range of (today, today - 5 years) then the Gauge turns green (100%), otherwise it stays red (0%)?

 

[ShameDonkey]

Of course. I have roughly 1500 items that require a maintenance date within the past five years to be compliant. I have a column with the date this maintenance occurs (Last Known Service Date) in one data set and a location by Region (Region) in another data set linked through an admin number.

Would like to be able to see where each region stands using the gas gauge display as a percent of total that is compliant based on the total number of these items. So if a region has 50% of their items with a maintenance date within five years their gauge would be half way or amber.

 

[JustynaMK]

Sheesh, I gave you quite a delay in my answer... I'm very sorry!

In case you still need it, here's one solution that you could try -

I brought in some random 1500 items organized in two tables, as per the screenshot below. The Service Date is a random date between today and 10 years ago.

In PowerBI, you can go to your Table Data and create a new column (be careful not to use "new measure" option):

Last5Y =
var ThisDate = Table1[Last Known Service Date]
var FiveYearsAgo = DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY()))
return
ThisDate >= FiveYearsAgo

Now that we have a new column, we can create a measure ("new measure" option) that will check the % of "True" flags for a selected region:

Last5Y% =
COUNTROWS(
FILTER(
Table1,
Table1[Last5Y] = True)
)
/
COUNTROWS(
Table1
)

Here's an example for EU:

It matches what I have calculated in Excel: