DAX Measure to calculate totat year to date volumes and use them in a table matrix with filters

[Anonymous]

Can I get help with a DAX measure to calculate YTD volumes for last year sales and current sales (in the three months that have passed and the formula needs to adjust everytime the month moves) by region to use in a matrix table where I have filters on the current month to show the following;

An example, I have a matrix table where I have 

1. Forecast

2. Current Mth sales

3. Open O

4.Estimates

5. The difference between Estimate vs Forecast

This table is filtered by Region and calender date.

Here is the measure I have that calculates total sales 

Total Actual = CALCULATE

(SUM('Sales Data file xlsx'[Volume (HL)]),

FILTER('Sales Data file xlsx',

'Sales Data file xlsx'[Key] = "IMSActual"

|| 'Sales Data file xlsx'[Key] = "FOBActual"))

Here is the measure I have that calculates last year sales 

Total LY2024 = CALCULATE

(SUM('Sales Data file xlsx'[Volume (HL)]),

FILTER('Sales Data file xlsx',

'Sales Data file xlsx'[Key] = "LY2024" ))

[v-pgoloju · Accepted solution]

Hi @Anonymous ,

Could you please share the measure and mark it as the accepted solution? This would greatly help other community members in resolving similar issues more efficiently.

Thanks

[Anonymous]

Hi there.

Here is the measure ;

YTD Hist =

VAR SelectedMonth = MONTH(MAX('Sales Data file xlsx'[Calender Date]))

VAR SelectedYear = YEAR(MAX('Sales Data file xlsx'[Calender Date]))

VAR StartMonth = MAX(1, SelectedMonth - 3)

VAR EndMonth = SelectedMonth - 1

VAR TargetYear = SelectedYear - 1

RETURN

IF (

    HASONEVALUE('Sales Data file xlsx'[Region]),

    -- Row-level logic

    CALCULATE(

        [Total LY2024],

        FILTER(

            ALL('Sales Data file xlsx'),

            YEAR('Sales Data file xlsx'[Calender Date]) = TargetYear &&

            MONTH('Sales Data file xlsx'[Calender Date]) >= StartMonth &&

            MONTH('Sales Data file xlsx'[Calender Date]) <= EndMonth &&

            'Sales Data file xlsx'[Region] = SELECTEDVALUE('Sales Data file xlsx'[Region])

        )

    ),

    -- Subtotal logic

    CALCULATE(

        [Total LY2024],

        FILTER(

            ALL('Sales Data file xlsx'),

            YEAR('Sales Data file xlsx'[Calender Date]) = TargetYear &&

            MONTH('Sales Data file xlsx'[Calender Date]) >= StartMonth &&

            MONTH('Sales Data file xlsx'[Calender Date]) <= EndMonth

        ),

[v-pgoloju]

Thank you @Greg_Deckler  for Your response.

Hello @Anonymous ,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Could you kindly provide a sample of your data? This will help ensure the DAX measure aligns accurately with your dataset.

ThankYou.

[Anonymous]

Hi thank you I actually figured the measure.

[Greg_Deckler]

@Anonymous Sample data would help. You can also give this a whirl. https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Better-Year-to-Date-Total/m-p/2875532#M908