cancel
Showing results for
Did you mean:
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 :

 Rating Company 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
// 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
// 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
// dax.guide/datesinperiod
// to see what it means.
Dates[Date],
LASTDATE( Dates[Date] ),
-2,
MONTH
),
DATESINPERIOD(
// Dates must be a proper
// 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
// dax.guide/datesinperiod
// to see what it means and
// how to build it.
Dates[Date],
LastDateBeforeOrOnToday,
-2,
MONTH
),
DATESINPERIOD(
// Dates must be a proper
// dax.guide/datesinperiod
// to see what it means and
// how to build it.
Dates[Date],
LastDateBeforeOrOnToday,
-1,
MONTH
)
)
)
)
return
Result``````

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
// 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
// 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
// dax.guide/datesinperiod
// to see what it means.
Dates[Date],
LASTDATE( Dates[Date] ),
-2,
MONTH
),
DATESINPERIOD(
// Dates must be a proper
// 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
// dax.guide/datesinperiod
// to see what it means and
// how to build it.
Dates[Date],
LastDateBeforeOrOnToday,
-2,
MONTH
),
DATESINPERIOD(
// Dates must be a proper
// dax.guide/datesinperiod
// to see what it means and
// how to build it.
Dates[Date],
LastDateBeforeOrOnToday,
-1,
MONTH
)
)
)
)
return
Result``````

Announcements

#### The Power BI Community Show

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

#### Ted's Dev Camp - July 28, 2022

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

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