Forum Discussion

DutchMark's avatar
DutchMark
Icon for Helper III rankHelper III
1 year ago
Solved

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

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([Revenu...
Need Help
Tips and Tricks
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    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.

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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's avatar
DutchMark
Icon for Helper III rankHelper III
1 year ago

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





  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    You’ll need to rely on one or more measures that respond to slicer selections, rather than depending on the buckets in your date calendar. Measures can modify the filter context and influence which rows are visible—rows without values in the context of the measure may appear hidden. However, slicers from a related dimension can override that and determine the visible rows.

     

    You can use field parameters to switch between different measures like in the image below.

    Please see the attached sample pbix.

     

    • DutchMark's avatar
      DutchMark
      Icon for Helper III rankHelper III
      1 year ago

      Thank you, but I really need them next to each other the years (like I have now) and I've seen this in models berore that it's possible.