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 ago
[Edit] Sorry, this does something different... but I'll post the right solution in a moment. But try to understand this logic as well as it can be useful 🙂
// 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]
[YTD YoY %] =
// 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 vYtdPeriod =
// This logic is needed in order to get
// correct comparisons for periods that
// have not yet finished.
CALCULATETABLE(
DATESYTD(
Dates[Date]
),
KEEPFILTERS(
Dates[Date] <= vLastDayWithData
)
)
var vYtdPeriodLastYear = SAMEPERIODLASTYEAR( vYtdPeriod )
var vYtdValue =
CALCULATE(
[Measure],
vYtdPeriod
)
var vYtdValueLastYear =
CALCULATE(
[Measure],
vYtdPeriodLastYear
)
var vChange =
DIVIDE(
vYtdValue - vYtdValueLastYear,
vYtdValueLastYear
)
return
vChange