Month on Month / Year on Year Price Comparisons

[joshbonner1986]

Hi All, 

I have a materials table, with 1 set of pricing (last price paid);

I want to create a table visual that when plotted against a time frame will show me the Month on Month comparisson. 

EG Input Data Table

Material	Paid	Date
123	£10	01/12/2024
123	£20	01/12/2024
123	£20	01/01/2025
123	£40	01/02/2025
123	£50	01/03/2025

my issue is, there is a lot of data and i only want to show last 3 months. But when i apply my current filters to the data the measure  for month 1 is lost (its filtered out) 

My current measure uses the calculation of;

Current sales = Sum(paid)

Sales in previous month = Calcualate(current sales), Parellperiod(Material, Material[Date], -1, MONTH)

% Diff = divide(Current sales - Sales in previous month, Current Sales)

so it shows the below

Material	%Diff	Date
123		01/01/2025
123	100%	01/02/2025
123	25%	01/03/2025

I need a measure to show that difference that is missing for whatever date is filtered first

Cheers

Josh

[v-kathullac]

Hi @joshbonner1986  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @joshbonner1986  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @joshbonner1986  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @joshbonner1986  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-prasare · Accepted solution]

Hi @joshbonner1986,

Thank you for reaching out to the Microsoft Community Forum.

Current month sales DAX code

Current Sales = SUM('Materials'[Paid])

Previous month sales DAX code

Sales in Previous Month = 
CALCULATE(
    [Current Sales],
    PARALLELPERIOD('Materials'[Date], -1, MONTH),
    ALLSELECTED('Materials'[Date])  
)

% Difference Measure (Handling Blank Values) DAX code

% Diff = 
VAR Current = [Current Sales]
VAR Previous = [Sales in Previous Month]
RETURN
IF(
    ISBLANK(Current) || ISBLANK(Previous),
    BLANK(),
    DIVIDE(Current - Previous, Previous)
)

Above DAX code handles situations where either the current or previous month is blank.
If both are blank, it won’t show errors.

You are trying to show the last 3 months Data

Material Paid Date
123      10   01/12/2024
123      20   01/12/2024
123      20   01/01/2025
123      40   01/02/2025
123      50   01/03/2025

If select last 3 months data (mar 2025,feb 2025, jan 2025)

The calculation as below.

for March month % Diff =Divide(50-40,50)=20%
For Feb month % Diff= Divide(40-30,40)=50%
For Jan month % Diff= Divide(20-20,20)=0%

so the result is

Material % Diff Date

123  20%  01/03/2025
123  50%  01/02/2025
123       01/01/2025

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[ryan_mayu]

@joshbonner1986 

how you get the %diff? why 20 to 40 is 50%, and 40 to 50 is 25 %?