cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Weighted Result based on Recent Month

NameMonthRating(1-5) Current  Month = 60% weighted   
SafetyJanuary1 -1 Month = 30% Weighted 
SafetyFebruary1 -2 Month = 10% Weighted 
SafetyMarch5   
SafetyApril5 March's  Safety Rating =3.4
CollaborationJanuary5 April's Safety Rating =4.6
CollaborationFebruary1   
CollaborationMarch5 March's Collaboration Rating =3.8
CollaborationApril4 April's Collaboration Rating =

4

 

 

In Dax, anyone can tell me how to get the result of the march's safety and collaboration rating when March is select,  and when April is selected, April result get output or what would be the best solution?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Weighted Result based on Recent Month

Step 1 - convert your "month" column to a date (first of the month); create a relationship to a date table

 

Step 2 - 3 measures

 

PM Rating = CALCULATE(SUM(Weighted[Rating]), PREVIOUSMONTH(Weighted[Dt]))

PPM Rating = CALCULATE(SUM(Weighted[Rating]), PREVIOUSMONTH(PREVIOUSMONTH(Weighted[Dt])))

This Month Rating = SUM(Weighted[Rating])*.6 + [PM Rating]*.3 + [PPM Rating]*.1

Capture.PNG

 

You may want to do some "IF (ISBLANK ( " logic in the measures so that you don't get values for months that don't have 2 previous months.

 

Hope this helps,

David




Proud to be a Super User!




View solution in original post

1 REPLY 1
Highlighted
Super User I
Super User I

Re: Weighted Result based on Recent Month

Step 1 - convert your "month" column to a date (first of the month); create a relationship to a date table

 

Step 2 - 3 measures

 

PM Rating = CALCULATE(SUM(Weighted[Rating]), PREVIOUSMONTH(Weighted[Dt]))

PPM Rating = CALCULATE(SUM(Weighted[Rating]), PREVIOUSMONTH(PREVIOUSMONTH(Weighted[Dt])))

This Month Rating = SUM(Weighted[Rating])*.6 + [PM Rating]*.3 + [PPM Rating]*.1

Capture.PNG

 

You may want to do some "IF (ISBLANK ( " logic in the measures so that you don't get values for months that don't have 2 previous months.

 

Hope this helps,

David




Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors