YTD Delta % with a custom start month

[Adam_Cromie]

Hi,

Hoping for assistance with a query;

I need a % change/delta between the most recent month and the month at the start of the year, where the start of the year begins in September. 

The data has different org's that the delta's need to split.

Below is the example data and result I'm trying to create:

Org	Period	Total
100	Aug-23	200
100	Sep-23	210
100	Oct-23	230
100	Nov-23	200
100	Dec-23	250
100	Jan-24	220
200	Aug-23	500
200	Sep-23	510
200	Oct-23	490
200	Nov-23	480
200	Dec-23	470
200	Jan-24	430

When reporting for Jan-24, the results should look like: 

Org	YTD Total Change
100	4.8%
200	-14.0%

Thanks in advance!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

One of ways to create solution is to have fiscal month number and fiscal year columns in the calendar table (or period table).
Please check the below picture and the attached pbix file.

expected result measure: =
VAR _latestmonth =
    MAXX ( SUMMARIZE ( Data, Period[Period] ), Period[Period] )
VAR _latestfiscalmonth =
    MAXX (
        FILTER ( Period, Period[Period] = _latestmonth ),
        Period[Fiscal Month Number]
    )
VAR _fiscalyear =
    MAXX ( SUMMARIZE ( Data, Period[Fiscal Year] ), Period[Fiscal Year] )
VAR _latestvalue =
    CALCULATE (
        SUM ( Data[Total] ),
        Period[Fiscal Month Number] = _latestfiscalmonth,
        Period[Fiscal Year] = _fiscalyear
    )
VAR _firstvalue =
    CALCULATE (
        SUM ( Data[Total] ),
        Period[Fiscal Month Number] = 1,
        Period[Fiscal Year] = _fiscalyear
    )
RETURN
    DIVIDE ( _latestvalue - _firstvalue, _firstvalue )

[Adam_Cromie]

This worked perfectly, thanks Jihwan

[RossEdwards]

I'd recommend making sure your model has a calendar table and inside that calendar table you have a "Fiscal Month" column which numerically holds the numbers 1 to 12 and a "Fiscal Year" column.  1 in your case would be September.  From here you could have a measure like the below.  Place this measure within your usual Org context with your monthly periods.

YTD Total Change = var currentFiscalYear = SELECTEDVALUE('Calendar'[Fiscal Year])
var currentResult = SUM('Data'[Total])
var firstResult = CALCULATE(
    SUM('Data'[Total]),
    ALL('Calendar'),
    FILTER(
        'Calendar'
        'Calendar'[Fiscal Year] = currentFiscalYear &&
        'Calendar'[Fiscal Month] = 1
    )
)
RETURN
DIVIDE(currentResult, firstResult)