Re: Help with Measure Calculation

Anonymous · ‎02-19-2021

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.

I have calculate my total as such: Sum of dealers selling 8 or more product groups = SUM('18 V'[Dealers Selling 8 or more product Groups])

and I just wish to (minus) one month worth of data from that total, in order to then calculate the % change between those two.

daxer-almighty · ‎02-20-2021

Here'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

daxer-almighty · ‎02-20-2021

[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