Forum Discussion
Anonymous
5 years agoNot applicable
Help with Measure Calculation
I am trying to calculate a % change between YTD data and previous month data. that isa calculation that is the percentage change between all my data to date and all my data until previous month. ...
daxer-almighty
Solution Sage
5 years agoHere's the correct solution, I believe. But I have not yet checked this on a real model. I've just written it...
// Assumptions:
// 1. There's a proper Dates table in the model (marked as a Date table in the model).
// 2. The main fact table is called FactTable.
// 3. Dates[Date] 1:* FactTable[Date]
// 4. In the Dates table there's a column that numbers years. Lets'
// call it YearNumber (e.g., 2020, 2021...). It should be an integer
// and will most likely be hidden. What will be exposed as the
// year will be a string column like with entries like "CY 2020",
// "CY 2021"...
[YTD MoM %] =
// Dates[Date] is of the datetime datatype without
// the time component. It's the primary key in
// the Dates table.
var vLastDayWithData =
CALCULATE(
MAX( FactTable[Date] ),
REMOVEFILTERS( )
)
var vMaxVisibleYear = MAX( Dates[YearNumber] )
var vYtdPeriod =
// This logic is needed in order to get
// correct comparisons for periods that
// have not yet finished. For instance,
// if you've got data going up to 20 Mar 2020,
// then you want to compare Mar YTD against
// Feb YTD but the calculation must go to
// 20 Feb 2020, not 28 Feb 2020 since that
// would distort the calculation because
// Mar 2020 has not yet finished. This logic
// accounts for this case.
CALCULATETABLE(
DATESYTD(
Dates[Date]
),
KEEPFILTERS(
Dates[Date] <= vLastDayWithData
)
)
var vYtdPeriodLastMonth =
CALCULATETABLE(
DISTINCT( Dates[Date] ),
DATEADD( vYtdPeriod, -1, MONTH ),
Dates[YearNumber] = vMaxVisibleYear
)
var vYtdValue =
CALCULATE(
[Measure],
vYtdPeriod
)
var vYtdValueLastMonth =
CALCULATE(
[Measure],
vYtdPeriodLastMonth
)
var vChange =
DIVIDE(
vYtdValue - vYtdValueLastMonth,
vYtdValueLastMonth
)
return
vChange