Comparing last 12 months to previous 12 months (partial problem)

[DutchMark]

I compare the last 12 months with the previous 12 months. 

I use 2 measurments:

Revenue this year PM = SUM(KPI_EOL_sync_financial_transactionlines[Revenue])

Revenue LY PM = CALCULATE([Revenue this year PM], SAMEPERIODLASTYEAR('Calendar Table'[Date]))

I filter on my Calendar Table.
Where I dynamically determine the last 12 months and so on

I get this column diagram. The problem is however even if I select Jan-24, I still see Jan-25 in the label

This gives the following problem, where I see in the "Full year comaprison" only 2025. I stead of 2024 en 2025 (If I remove the filter I see all the years). How can I fix this?

[DutchMark]

Everyone seems to read past what I'm asking and read past what I'm looking for.
- The filters need to be in the formula
- If I hover over Jan-25, the previous year needs to show in the LABEL Jan-24 and this year Jan-25
- Don't forgot the Matrix in the bottom. This is what it's all about. Because I need to be able to select both Jan-24 and Jan-25 seperately to see them next to each other. I don't see that now!

[DutchMark]

No one?

[v-kpoloju-msft]

Hi @DutchMark,

Apologize for the delayed response. Thanks for the follow-up question. I again reproduced the scenario with my sample data with combo chart diagram. I got this outcome below shown in the image.

Attached is the .pbix file. Could you kindly review it. If the issue continues, kindly provide more precise sample data along with the expected behaviour. This will help us resolve your issue more efficiently.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[DutchMark]

Thank you, but it really needs to look like the chart I already shared and it doesn't look like that at all. More sample data is not going to help. I need a way to put 2 years next to eacht other per month, like shared in the first chart.

[v-kpoloju-msft]

Hi @DutchMark,

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Output:

Note:  if you want to use this report, please have a slicer with year filter where you will get previous year to the current year comparison. dynamically determine the last 12 months measure is causing the problem

 
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[DutchMark]

Thank you, but I want to do it dynamically. I don't know maybe I'm not clear enough. Your chart has the same problem. In 2024-02 both charts called 2024-02 if you go over it. I want them to have the right name, so 2024-02 en and 2023-02.

Also again, my outcome is just fine, I just want those labels to be correct when I go over them. 

[v-kpoloju-msft]

Hi @DutchMark,

After thoroughly reviewing the details you provided, I was able to reproduced the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it. It dynamically updates the values when modifications occur in the slicer.

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[DutchMark]

Thank you, but I guess this topic is not going to give the outcome I want.

I have repeatly stated what I'm looking for, but not one solution gives this reply. I give up and keep my current chart. It's not worth the time anymore. Thank you for replying. 

"Thank you, but I want to do it dynamically. I don't know maybe I'm not clear enough. Your chart has the same problem. In 2024-02 both charts called 2024-02 if you go over it. I want them to have the right name, so 2024-02 en and 2023-02."

[v-kpoloju-msft]

Hi @DutchMark,

Apologize for the delayed response could you please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you for your understanding and participation.

[DutchMark]

Hi, sorry, but that would be so much effort for I think what would be a standard solution which should be clear from a screenshot and everything I already wrote. 

Don't think sample date is going to change the outcome. Because even after typing in precisly what I want I get other solutions. I don't think is going to change with sample data. 

[v-kpoloju-msft · Accepted solution]

Hi @DutchMark,

We sincerely apologize for the inconvenience caused.

As the issue is still not yet resolved, we kindly request you to raise an issue at Issues - Microsoft Fabric Community for investigation.
Otherwise raise a Microsoft support ticket using the link provided below. They will be able to provide specific insights related to your account and potential resolutions.
Microsoft Fabric Support and Status | Microsoft Fabric

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[v-kpoloju-msft]

Hi @DutchMark,

Could you kindly confirm if you have raised a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you for using the Microsoft Community Forum.

[DutchMark]

I have some other priorities. I will have a look at it later. 

[v-kpoloju-msft]

Hi @DutchMark,

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[DutchMark]

This is a bit annoying. Don't keep pushing. I already ansewered you.

[v-kpoloju-msft]

Hi @DutchMark,

Thank you for your update. We sincerely apologize for any inconvenience caused. Please feel free to revisit the thread at your convenience your priorities are fully respected. If you have any questions or need further assistance in the future, we’ll be here to help.

Thank you for your understanding and using the Microsoft Community Forum.

[DutchMark]

Like this, you see an example

https://learn.microsoft.com/nl-nl/power-bi/visuals/power-bi-visualization-combo-chart?tabs=powerbi-d...

[v-kpoloju-msft]

Hi @DutchMark,
Thank you for reaching out to the Microsoft fabric community forum. Additionally, the solution proposed by the super user @danextian, is correct according to the thread.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Output:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[danextian]

That is the expected behavior. SAMEPERIODLASTYEAR does not alter the visible rows; instead, it adjusts the filter context relative to the visible rows. So, if you select the past 12 months in your calendar month bucket, those months will be shown. SAMEPERIODLASTYEAR will then return the values for the corresponding period in the previous year.

[DutchMark]

Okay but I was expect because it shows both last 12 months and the 12 months before that, that it will show the 2 months in both years. 

I also have other measurements (that I don't use), but I can not seem to get them in the same Column table next to each other. Any tips? Will that get my desired result? Most important for me is that the "Full year comparison" will show both 2025 en 2024 when I select the month in the column diagram. That's a matrix table by the way.

Last_12_Months_Revenue =

VAR CurrentMonth = MAX('Calendar Table'[Current Month])

RETURN

IF(

    MAX('Calendar Table'[Date]) <= CurrentMonth &&

    MAX('Calendar Table'[Date]) >= EDATE(CurrentMonth, -12),

    SUM(KPI_EOL_sync_financial_transactionlines[Revenue]),

    BLANK()

)
---

Previous_12_Month_Window_Revenue =

VAR CurrentMonth = MAX('Calendar Table'[Current Month])

VAR WindowStart = EDATE(CurrentMonth, -24)

VAR WindowEnd = EDATE(CurrentMonth, -12)

RETURN

IF(

    MAX('Calendar Table'[Date]) <= WindowEnd &&

    MAX('Calendar Table'[Date]) > WindowStart,

    SUM(KPI_EOL_sync_financial_transactionlines[Revenue]),

    BLANK()

)