Comparison between months using set analysis

[Amit_B]

Hi everyone,

I need to create a line chart that compares each month to its previous month.
The dimension is Year-Month (format: YYYY-MM).
The metric should be the quantity of the month compared to the quantity of its previous month. There is a master measure called CountX that can be used.

For example:
The model is filtered for the year 2025.
CountX for February 2025 is 100, for January 2025 is 80, and for December 2024 is 95.
Then in January 2025 I should see -15 (80-95), and in February 2025 I should see 20 (100-80).

I tried using the Above function, but I am not getting data for January 2025, probably because December 2024 doesn't meet the filter criteria.
I couldn't solve it using set analysis.

I would appreciate any help.
Thanks!

[ramchalla]

Hi Amit,

You can use the below expression. Please check if this helps.

Sum({<YearMonth>}Sales)-Below(Sum({<YearMonth>}Sales))

you can replace Sum with Count and Sales with Quantity based on your requirement.

[Amit_B]

I tried to do it.
For Above I am not getting data for March 2025.
For Below I am not getting data for January 2025.

[ramchalla]

Hi Amit,

Can you please let me know the value should be displayed for the last month? Based on the below function, we will not get any value for the last month. Should it be the same value for the last month?

And if possible, can you please help me with the sample data?

[Amit_B]

Hi,

Let's say we have 2 simple tables:
* Calendar - Date, Year, Quarter, MonthID, YearMonth (YYYY-MM)...
* Sales - SaleDate (as Date), StoreID, InvoiceNo, Line, ItemID...

CountX = Count(distinct InvoiceNo)

YearMonth 2024-10 2024-11 2024-12 2025-01 2025-02 2025-03 CountX 36,840 42,941 39,097 37,762 45,394 13,864

I need to present the change for each month compared to the previous month.
My measure should be something like that:
CountX - ({<YearMonth={[set analysis for previous month]}>}CountX)

For the data above:
For 2025-03: 13,864 - 45,394 = -31,530
For 2025-02: 45,394 - 37,762 = 7,632
For 2025-01: 37,762 - 39,097 = -1,335
For 2024-12: 39,097 - 42,941 = -3,844
And so..
Only for the first YearMonth (2023-01) we will not receive value because there is no data prior to it (for 2022-12).

I tried to use Below function and it's work if i don't filter.
But since my default filter is the current Year, I don't receive value for 2025-01.

I Hope I was clear.
Thank you for the help.