Measures to calculate the health index

[Dunner2020]

Hi there,

I am calculating two columns in a table (in power query): age and Health category.

Age column is the difference of installation date of the asset and today's date whereas the health category is the categorization of age values into 5 bands (i.e. H1, H2, H3,H4, and H5).  The recent or newer asset falls into H5 and the oldest one falls into H1.  

I also calculated five measures to calculate the percentage of H1, H2, H3, H4, and H5 of the asset. For example, for H1, the measure looks as follow:

H1 % = (CALCULATE(COUNT('Table'[ASSETID]),'Table'[Health Category]="H1")/COUNT('Table'[FACILITYID])) * 100

I plotted calculated measures as a stacked bar chart and display them as follow:

Now I want to create another bar chart that shows the condition of asset after 10 years which means whatever the age is now, I have to add 10 years more into it and recalculate the health category then calculate H1, H2, H3, H4, H5 for asset condition after 10 years. 

I am not sure how can I calculate asset condition after 10 years. Do I need to calculate two new columns related to age and health category after 10 year? I am not sure how could I calculate the after 10 years condition using measures. Any help would be really appreicated.

Sample file can be found here

[HashamNiaz]

Hi !

This can be achieved using What-If parameters, you can go back or forward the number of periods you want. Let's suppose you can give a choice to user to go forward 10 years, now you can use this parameter value to calculate Age after 10 years and rest of your calculation will be based on new Age.

If you have the sample .pbix we can share how it will work.

Regards,

Hasham

[Dunner2020]

@HashamNiaz , thanks for your reply. I have included the sample file in the original post.

[HashamNiaz]

Hi !

I have gone through the .pbix file. There are few changes i would recommend to the model.

Instead of Age Category being a measure, try to incapsulate the Age Category logic into column irrespective of Age. This Age Category column can be used in Axis to plot chart.

For Age, to be dynamic, i have created a What-If paremater using below DAX;

No. Of Years = GENERATESERIES(0, 70, 1)

Now user can make the selection from UI to select how many years they want to go forward, we will use this paramter to calculate dynamic Age using below DAX;

Age = 
var _installation = MAX('Table'[INSTALLATIONDATE])
var _futuredate = DATE(YEAR(TODAY()) + 'No. Of Years'[No. Of Years Value], MONTH(TODAY()), DAY(TODAY())) 
Return
CALCULATE(DATEDIFF(_installation, _futuredate,YEAR))

This will give you Age depending on your slicer selection. Now you can use this Age column to calculate H1, H2.

I am not sure about your H1 & H2 calculations if you provide more information we would be able to solve it.

Regards,

Hasham
 

[Dunner2020]

@HashamNiaz , thanks for your reply.

I have already created age column in the table.

I can use new created age measure to calclate the measure for calculating the health category. The formula for calculating health category is as follow:

if (([MATERIAL] = "CONC" or [MATERIAL] = "STEEL") and [Age] >= (75-1)) or (([MATERIAL] = "HW" or [MATERIAL] = "HWIB") and [Age] >= (60-1))or ([MATERIAL] = "SW" and [Age] >= (50-1)) then "H1" else
if (([MATERIAL] = "CONC" or [MATERIAL] = "STEEL") and [Age] < (75-1) and [Age] >=(75-3)) or (([MATERIAL] = "HW" or [MATERIAL] = "HWIB") and [Age] < (60-1) and [Age] >= (60-3))or ([MATERIAL] = "SW" and [Age] < (50-1) and [Age] >= (50-3)) then "H2" else
if(([MATERIAL] = "CONC" or [MATERIAL] = "STEEL") and [Age] < (75-3) and [Age] >=(75-10)) or (([MATERIAL] = "HW" or [MATERIAL] = "HWIB") and [Age] < (60-3) and [Age] >= (60-10))or ([MATERIAL] = "SW" and [Age] < (50-3) and [Age] >= (50-10)) then "H3" else
if(([MATERIAL] = "CONC" or [MATERIAL] = "STEEL") and [Age] < (75-10) and [Age] >=(75-20)) or (([MATERIAL] = "HW" or [MATERIAL] = "HWIB") and [Age] < (60-10) and [Age] >= (60-20))or ([MATERIAL] = "SW" and [Age] < (50-10) and [Age] >= (50-20)) then "H4" else
if(([MATERIAL] = "CONC" or [MATERIAL] = "STEEL") and [Age] < (75-20)) or (([MATERIAL] = "HW" or [MATERIAL] = "HWIB") and [Age] < (60-20))or ([MATERIAL] = "SW" and [Age] < (50-20)) then "H5" else""

However, I am still confuse that after calculating age and health category (using newly built measure), how am I going to calculate the H1%, H2% or so on? The typical formula for calculating H1% is 

H1 % = (CALCULATE(COUNT('Table'[FACILITYID]),'Table'[Health Category]="H1")/COUNT('Table'[FACILITYID])) * 100

In the updated case, I don't have age and Health categories as columns. They are present in the form of measure. How could I calculate the percentage of H1% when the Health category measure has "H1" as the output value.