Difference in sales from previous year end date to current year month end dates

[gmasta1129]

Hello,

My report contains the following columns: 

1. Month End Date (ex: 12/31/2024, 1/31/2025, 2/28/2025 etc.)

2. Portfolio Code

3. Sales Amount

4. Difference (new calculated column)

I would like to create a table which shows the difference in sales between each month of this year to the previous year end date (12/31/2024) by each portfolio code.  I need help with the formula for the "Difference" column.  

Please see example below for table I am looking to  create.  

Portfolio Code	Month End Date	Sales amount	Difference
123456	12/31/2024	5,000,000.00
123456	1/31/2025	4,000,000.00	(1,000,000.00)
123456	2/28/2025	15,000,000.00	10,000,000.00
123456	3/31/2025	2,000,000.00	(3,000,000.00)
123456	4/31/2025	13,000,000.00	8,000,000.00
123456	5/31/2025	18,000,000.00	13,000,000.00

[v-karpurapud]

Hi @gmasta1129 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[v-karpurapud]

Hi @gmasta1129 

We have not yet heard back from you about whether the response addressed your query. If it did not, please share more details so we can assist you more effectively.

Thank You.

[v-karpurapud]

Hi @gmasta1129 

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @MasonMA  for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? This will help other community members solve similar problems faster.

Thank you.

[gmasta1129]

Hello @MasonMA , 

Thank you for the quick response. I appreciate it.   

The above table I created contains sample data.  The full file contains 5 years + of data. 

Is it possible to change this part of the formula (see screenshot below) so its not set to 12/31/2024 but each year end date? For example, I'll need to compare 12/31/2023 with each month end in 2024, and 12/31/2022 with each month end in 2023, and 12/31/2021 with each month end in 2022.

    'SalesTable'[Month End Date] = DATE(2024, 12, 31)

[MasonMA · Accepted solution]

Hi

If more than 5 years, i'd suggest you use measures based on your evaluation contexts in actual report. 

If you still prefer to use calculated columns, yes you can generalize it by updating variables.

VAR CurrentPortfolio = 'SalesTable'[Portfolio Code]
VAR CurrentDate = 'SalesTable'[Month End Date]
VAR CurrentYear = YEAR(CurrentDate)
VAR PreviousYearEnd = DATE(CurrentYear - 1, 12, 31)

Then replace the 'DATE(2024, 12, 31)' with 'PreviousYearEnd'

Hope this helps~

[MasonMA]

@gmasta1129 

Hi, build a new column with below DAX

Difference = 
VAR CurrentPortfolio = 'SalesTable'[Portfolio Code]

VAR BaselineSales =
    CALCULATE(
        MAX('SalesTable'[Sales Amount]),
        FILTER(
            'SalesTable',
            'SalesTable'[Month End Date] = DATE(2024, 12, 31)
            && 'SalesTable'[Portfolio Code] = CurrentPortfolio
        )
    )

RETURN
    'SalesTable'[Sales Amount] - BaselineSales