cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deb_power123
Helper V
Helper V

DAX to find KPI of ratings per month

Hi @Anonymous 

 

Hi All, 

 

I have a table company as below . Ratings column shows the company ratings from -5 to 5 range.

 

 The current month now is July and it is not completed so whatever is the current system date say 16-jul-2021(system date today) we need to take range till 16-Jun2021 for 30 days to complete the current month.We need to find the current month KPI reputation average per company say today is 16-07-2021(system date) to 16-06-2021 is the current rating average KPI per company and then from 15-06-2021 to 15-05-2021 is previous month rating average KPI per company.

 

 I need to find the current month average rating KPI for the companies[A,B,C ,D]  and previous month average rating KPI for the companies[A,B,C,D] .

 

Could anyone please suggest any DAX to handle this ?

 

My Input source table :

 

RatingCompany     Date
 2   A 16.07.2021
 2   A 15.07.2021
 2   A 13.07.2021
 1   A 12.07.2021
-1   A 11.07.2021
 0   A 10.07.2021
 1   B 16.07.2021
 1   B 15.07.2021
 4   B 13.07.2021
 2   B 12.07.2021
 2   B 11.07.2021
-1   B 10.07.2021
 1   C 16.07.2021
 1   C 15.07.2021
 4   C 13.07.2021
-2   C 12.07.2021
 2   C 11.07.2021
 3   C 10.07.2021
 1   D 12.06.2021
 4   D 11.06.2021
 3   D 10.06.2021
 1   B 10.06.2021
 4   B 14.06.2021
 2   B 15.06.2021
 2   B 13.06.2021
-1   B 12.06.2021
 1   C 14.06.2021
 1   C 10.05.2021
 4   C 12.05.2021
-2   C 11.05.2021
 2   C 10.05.2021
 3   C 11.05.2021
 1   D 10.05.2021
 4   D 11.05.2021

 

Kind regards

Sameer

1 ACCEPTED SOLUTION
Anonymous
Not applicable

 

// In your Dates table you should have
// a column that will hold a boolean (0 or 1)
// and it'll be 1 if the current date in
// the row is <= today and 0 otherwise.
// Then you'll filter any visual/page/report
// by the dates that are assigned true.
// You'll do it via the Filter Pane. This is 
// the most flexible design.

[Current Rating KPI (relative)] =
// This will be the average over companies
// of averages of their ratings. If one
// company is selected, it'll be its average
// rating. Of course, this measure is responsive
// to slicers. The current month is the month
// that is the last one in the current context.
AVERAGEX(
	DISTINCT( FactTable[Company] ),
	CALCULATE(
		AVERAGE( FactTable[Rating] ),
		DATESINPERIOD(
			// Dates must be a proper
			// calendar. Please see
			// dax.guide/datesinperiod
			// to see what it means.
			Dates[Date],
			LASTDATE( Dates[Date] ),
			-1,
			MONTH
		)
	)
)

[Current Rating KPI (absolute)] =
// This will be the average over companies
// of averages of their ratings in the last
// month in your Date table for which the column
// with true/false as above is true. It'll 
// be insensitive to any filters on Dates
// but will respect all the other filters.
// You can rename the variables later to something
// more meaningful.
var LastDateBeforeOrOnToday =
	CALCULATE(
		MAX( Date[Date] ),
		// This is the column I'm talking about
		// above.
		Date[IsBeforeOrOnToday] = 1,
		REMOVEFILTERS( )
	)
var Result =
	CALCULATE(
		[Current Rating KPI (relative)],
		DATESINPERIOD(
			// Dates must be a proper
			// calendar. Please see
			// dax.guide/datesinperiod
			// to see what it means and
			// how to build it.
			Dates[Date],
			LastDateBeforeOrOnToday,
			-1,
			MONTH
		)
	)
)
return
	Result
	

[Previous Rating KPI (relative)] =
// Same as relative above but with
// dates moved back 1 month.
AVERAGEX(
	DISTINCT( FactTable[Company] ),
	CALCULATE(
		AVERAGE( FactTable[Rating] ),
		EXCEPT(
			DATESINPERIOD(
				// Dates must be a proper
				// calendar. Please see
				// dax.guide/datesinperiod
				// to see what it means.
				Dates[Date],
				LASTDATE( Dates[Date] ),
				-2,
				MONTH
			),
			DATESINPERIOD(
				// Dates must be a proper
				// calendar. Please see
				// dax.guide/datesinperiod
				// to see what it means.
				Dates[Date],
				LASTDATE( Dates[Date] ),
				-1,
				MONTH
			)		
		)
	)
)

[Previous Rating KPI (absolute)] =
// This will be the average over companies
// of averages of their ratings in the last
// month in your Date table for which the column
// with true/false as above is true. It'll 
// be insensitive to any filters on Dates
// but will respect all the other filters.
// You can rename the variables later to something
// more meaningful.
var LastDateBeforeOrOnToday =
	CALCULATE(
		MAX( Date[Date] ),
		// This is the column I'm talking about
		// above.
		Date[IsBeforeOrOnToday] = 1,
		REMOVEFILTERS( )
	)
var Result =
	CALCULATE(
		[Current Rating KPI (relative)],
		EXCEPT(
			DATESINPERIOD(
				// Dates must be a proper
				// calendar. Please see
				// dax.guide/datesinperiod
				// to see what it means and
				// how to build it.
				Dates[Date],
				LastDateBeforeOrOnToday,
				-2,
				MONTH
			),
			DATESINPERIOD(
				// Dates must be a proper
				// calendar. Please see
				// dax.guide/datesinperiod
				// to see what it means and
				// how to build it.
				Dates[Date],
				LastDateBeforeOrOnToday,
				-1,
				MONTH
			)
		)
	)
)
return
	Result

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

 

// In your Dates table you should have
// a column that will hold a boolean (0 or 1)
// and it'll be 1 if the current date in
// the row is <= today and 0 otherwise.
// Then you'll filter any visual/page/report
// by the dates that are assigned true.
// You'll do it via the Filter Pane. This is 
// the most flexible design.

[Current Rating KPI (relative)] =
// This will be the average over companies
// of averages of their ratings. If one
// company is selected, it'll be its average
// rating. Of course, this measure is responsive
// to slicers. The current month is the month
// that is the last one in the current context.
AVERAGEX(
	DISTINCT( FactTable[Company] ),
	CALCULATE(
		AVERAGE( FactTable[Rating] ),
		DATESINPERIOD(
			// Dates must be a proper
			// calendar. Please see
			// dax.guide/datesinperiod
			// to see what it means.
			Dates[Date],
			LASTDATE( Dates[Date] ),
			-1,
			MONTH
		)
	)
)

[Current Rating KPI (absolute)] =
// This will be the average over companies
// of averages of their ratings in the last
// month in your Date table for which the column
// with true/false as above is true. It'll 
// be insensitive to any filters on Dates
// but will respect all the other filters.
// You can rename the variables later to something
// more meaningful.
var LastDateBeforeOrOnToday =
	CALCULATE(
		MAX( Date[Date] ),
		// This is the column I'm talking about
		// above.
		Date[IsBeforeOrOnToday] = 1,
		REMOVEFILTERS( )
	)
var Result =
	CALCULATE(
		[Current Rating KPI (relative)],
		DATESINPERIOD(
			// Dates must be a proper
			// calendar. Please see
			// dax.guide/datesinperiod
			// to see what it means and
			// how to build it.
			Dates[Date],
			LastDateBeforeOrOnToday,
			-1,
			MONTH
		)
	)
)
return
	Result
	

[Previous Rating KPI (relative)] =
// Same as relative above but with
// dates moved back 1 month.
AVERAGEX(
	DISTINCT( FactTable[Company] ),
	CALCULATE(
		AVERAGE( FactTable[Rating] ),
		EXCEPT(
			DATESINPERIOD(
				// Dates must be a proper
				// calendar. Please see
				// dax.guide/datesinperiod
				// to see what it means.
				Dates[Date],
				LASTDATE( Dates[Date] ),
				-2,
				MONTH
			),
			DATESINPERIOD(
				// Dates must be a proper
				// calendar. Please see
				// dax.guide/datesinperiod
				// to see what it means.
				Dates[Date],
				LASTDATE( Dates[Date] ),
				-1,
				MONTH
			)		
		)
	)
)

[Previous Rating KPI (absolute)] =
// This will be the average over companies
// of averages of their ratings in the last
// month in your Date table for which the column
// with true/false as above is true. It'll 
// be insensitive to any filters on Dates
// but will respect all the other filters.
// You can rename the variables later to something
// more meaningful.
var LastDateBeforeOrOnToday =
	CALCULATE(
		MAX( Date[Date] ),
		// This is the column I'm talking about
		// above.
		Date[IsBeforeOrOnToday] = 1,
		REMOVEFILTERS( )
	)
var Result =
	CALCULATE(
		[Current Rating KPI (relative)],
		EXCEPT(
			DATESINPERIOD(
				// Dates must be a proper
				// calendar. Please see
				// dax.guide/datesinperiod
				// to see what it means and
				// how to build it.
				Dates[Date],
				LastDateBeforeOrOnToday,
				-2,
				MONTH
			),
			DATESINPERIOD(
				// Dates must be a proper
				// calendar. Please see
				// dax.guide/datesinperiod
				// to see what it means and
				// how to build it.
				Dates[Date],
				LastDateBeforeOrOnToday,
				-1,
				MONTH
			)
		)
	)
)
return
	Result

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors