Calculating rolling 12 months risk score

[Anonymous]

I am trying to calculate a risk score using rolling 12 months.  The risk scores I want to assign out are 0, 5, 10, 15.  The calculation would assign a 0 if the date is within 12 months, 5 if it is in the previous 12 months, 10 if in the previous 12 months after that, and 15 if in the previous 12 months after that or does not have a date.  Below is the data that I have and i want the risk score to be calcualted on the reporting date. 

Unit	Last Audit	Audit Age
Dogs	Sunday, January  1, 2017	3
Cats	Tuesday, January 1, 2019	1
Birds	Wednesday, January 1, 2020	0
Horses	Tuesday, January 1, 2019	1
Bunnies	Friday, January 1, 2016	4
Mice	Thursday, January 1, 2015	5

[V-pazhen-msft]

@Anonymous 

I guess this is not a rolling n months question, using Switch() is more appropriate, try this measure:

Measure = SWITCH(TRUE(),
SUM('Table'[Last Audit])>TODAY()-365, 0,
SUM('Table'[Last Audit])<TODAY()-365 && SUM('Table'[Last Audit])>TODAY()-365*2,5,
SUM('Table'[Last Audit])<TODAY()-365*2 && SUM('Table'[Last Audit])>TODAY()-365*3,10,
SUM('Table'[Last Audit])<TODAY()-365*3 && SUM('Table'[Last Audit])>TODAY()-365*4,15,15)

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

When i did that formula i got all 0s for every line item.  Below is what i used:

Rolling Last Audit = SWITCH(TRUE(),

SUM('Project Status'[Report Issue Date].[Date])>TODAY()-365, 0,

SUM('Project Status'[Report Issue Date].[Date])<TODAY()-365 && SUM('Project Status'[Report Issue Date].[Date])>TODAY()-365*2,5,

SUM('Project Status'[Report Issue Date].[Date])<TODAY()-365*2 && SUM('Project Status'[Report Issue Date].[Date])>TODAY()-365*3,10,

SUM('Project Status'[Report Issue Date].[Date])<TODAY()-365*3 && SUM('Project Status'[Report Issue Date].[Date])>TODAY()-365*4,15,15)

and I also tried:

Rolling Last Audit = SWITCH(TRUE(),

SUM('Project Status'[Report Issue Date])>TODAY()-365, 0,

SUM('Project Status'[Report Issue Date])<TODAY()-365 && SUM('Project Status'[Report Issue Date])>TODAY()-365*2,5,

SUM('Project Status'[Report Issue Date])<TODAY()-365*2 && SUM('Project Status'[Report Issue Date])>TODAY()-365*3,10,

SUM('Project Status'[Report Issue Date])<TODAY()-365*3 && SUM('Project Status'[Report Issue Date])>TODAY()-365*4,15,15)

Project Status is my table name and Report Issue Date is the column (its the same formate as Last Audit column in the orginal data i supplied)

[V-pazhen-msft · Accepted solution]

@Anonymous 
I guess you have create a column using the formula?

Check out the pbix attached, you should create a measure for this requirement.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Not very clear.

But you can can rolling 12 month value with date table like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

So i do not have any sales data.  I am performing a risk assessment so i have all these companies with their audit report ratings and the date the report went out.   I want to figure out how to perform a rolling 12 month calculation where it would assign a company  the following risk scores:

• 0 if the reporting date was within 12 months
• 5 if the reporting date was in the previous 12 months
• 10 if the reporting date was in the previous 12 months from that
• 15 if the reporting date was in the previous 12 months from that or does not have a reporting date at all (meaning it was never audited)