topic Re: DAX Measure to calculate totat year to date volumes and use them in a table matrix with filters in DAX Commands and Tips

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

Anonymous — Thu, 03 Apr 2025 18:49:22 GMT

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" ))

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

Greg_Deckler — Thu, 03 Apr 2025 21:17:37 GMT

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

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

Anonymous — Fri, 04 Apr 2025 05:25:08 GMT

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.

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

Anonymous — Fri, 04 Apr 2025 18:24:00 GMT

Hi thank you I actually figured the measure.

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

Anonymous — Tue, 08 Apr 2025 09:57:01 GMT

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

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

Anonymous — Tue, 08 Apr 2025 10:40:18 GMT

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