// 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
