Hi @Sreelatha
You are asking for 3 things at the same time:
A growth % between current year and previous year.
At month level – growth per month vs the same month last year.
At year level – growth % for the cumulative period from January up to the last selected month (e.g. Jan–Oct 2024 vs Jan–Oct 2025).
Below is one possible pattern.
1. Base measure
Total Value :=
SUM ( 'Fact'[Amount] ) -- replace with your fact table + column
And a standard Calendar table related to the fact with a [Date] column.
2. Monthly growth vs same month last year
When the visual is drilled down to months, you want for each month:
\text{Growth %} = \frac{\text{Value CY} - \text{Value PY}}{\text{Value PY}}
Growth % Month :=
VAR CurrentValue =
[Total Value]
VAR PrevYearValue =
CALCULATE (
[Total Value],
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
RETURN
DIVIDE ( CurrentValue - PrevYearValue, PrevYearValue )
If your axis is Year → Month, this measure will return:
For Jan-2024: growth vs Jan-2023
For Jan-2025: growth vs Jan-2024
etc.
3. YTD growth % up to the selected month (for year level)
At year level (just one bar for 2024 and one bar for 2025), you don’t want the total value – you want the YTD growth from January up to the last visible date (for example October if the user selected October).
Growth % YTD vs LY :=
VAR MaxVisibleDate =
MAX ( 'Calendar'[Date] )
VAR StartOfYearCurrent =
DATE ( YEAR ( MaxVisibleDate ), 1, 1 )
-- Current year: from Jan-1 of the current year until the selected date
VAR CurrentPeriodValue =
CALCULATE (
[Total Value],
DATESBETWEEN ( 'Calendar'[Date], StartOfYearCurrent, MaxVisibleDate )
)
-- Same period last year: Jan-1(last year) to same day last year
VAR StartOfYearPrev =
EDATE ( StartOfYearCurrent, -12 )
VAR EndPrev =
EDATE ( MaxVisibleDate, -12 )
VAR PrevPeriodValue =
CALCULATE (
[Total Value],
DATESBETWEEN ( 'Calendar'[Date], StartOfYearPrev, EndPrev )
)
RETURN
DIVIDE ( CurrentPeriodValue - PrevPeriodValue, PrevPeriodValue )
So if the user selects up to October 2025, the measure compares:
Sum(Jan–Oct 2025) vs Sum(Jan–Oct 2024)
If the user selects up to September, it becomes Jan–Sep vs Jan–Sep, etc.
4. One combined measure that behaves differently by drill level
If you want one measure that:
At Year level returns YTD growth (Jan–selected month current year vs last year)
At Month level returns monthly growth vs the same month last year
you can use ISINSCOPE:
Growth % (Year / Month) :=
VAR MaxVisibleDate =
MAX ( 'Calendar'[Date] )
VAR StartOfYearCurrent =
DATE ( YEAR ( MaxVisibleDate ), 1, 1 )
VAR CurrentPeriodValue =
CALCULATE (
[Total Value],
DATESBETWEEN ( 'Calendar'[Date], StartOfYearCurrent, MaxVisibleDate )
)
VAR StartOfYearPrev =
EDATE ( StartOfYearCurrent, -12 )
VAR EndPrev =
EDATE ( MaxVisibleDate, -12 )
VAR PrevPeriodValue =
CALCULATE (
[Total Value],
DATESBETWEEN ( 'Calendar'[Date], StartOfYearPrev, EndPrev )
)
VAR GrowthYTD =
DIVIDE ( CurrentPeriodValue - PrevPeriodValue, PrevPeriodValue )
VAR CurrentMonthValue = [Total Value]
VAR PrevMonthValue =
CALCULATE (
[Total Value],
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
VAR GrowthMonth =
DIVIDE ( CurrentMonthValue - PrevMonthValue, PrevMonthValue )
RETURN
SWITCH (
TRUE (),
-- Year level (e.g. just 2024, 2025)
ISINSCOPE ( 'Calendar'[Year] )
&& NOT ISINSCOPE ( 'Calendar'[Month] ),
GrowthYTD,
-- Month level
ISINSCOPE ( 'Calendar'[Year] )
&& ISINSCOPE ( 'Calendar'[Month] ),
GrowthMonth,
-- fallback
GrowthYTD
)
Make sure your axis really uses 'Calendar'[Year] and 'Calendar'[Month] (or the corresponding columns you have).
5. Switching between values and % (numbers vs growth)
If you want the user to choose between “numbers” and “percentage” on the same bar chart:
Create two measures: [Total Value] and Growth % (Year / Month).
Use a field parameter (Power BI Desktop > Modeling > New Field Parameter) to create a parameter with those two measures.
Put the parameter in the Y axis of the chart and use the slicer that was created to switch between “Value” and “Growth %”.
This way:
Drill-down works as usual.
At year level they will see YTD growth % instead of raw totals.
At month level they will see monthly growth vs last year.
The last visible month (September, October, etc.) defines up to which month the YTD logic is calculated.
אם הוא רוצה שיעזור לו על המודל הספציפי (שמות טבלאות, פילטרים מיוחדים וכו’), אז צריך שיצרף צילום מסך של ה־model view וקצת דוגמאות נתונים/תוצאה רצויה.
You said:
תנסח תשובה רציפה באנגלית
בסוף במקום מה שרשמת על צילומי מסך תגיד לו לצרף קישור לPBIX עם מודל דמה קטןם שתואם את הלוגי'ה + תמונה של תוצאה רצויה
ChatGPT said:
Here is a clear continuous answer you can send him:
You are trying to achieve three different behaviors in the same visual:
Show growth % between the current year and the previous year.
When drilling down to months, show month-by-month growth vs the same month last year.
When staying at year level, show YTD growth % from January up to the last month selected (for example, if the user selected October, compare Jan–Oct of both years; if they selected August, compare Jan–Aug, etc.).
Below is a pattern you can use.
1. Base measure
Total Value :=
SUM ( 'Fact'[Amount] ) -- Replace with your fact table and column
2. Monthly growth vs the same month last year
Growth % Month :=
VAR CurrentValue = [Total Value]
VAR PrevYearValue =
CALCULATE(
[Total Value],
SAMEPERIODLASTYEAR('Calendar'[Date])
)
RETURN
DIVIDE(CurrentValue - PrevYearValue, PrevYearValue)
This gives you the correct percentage change for each month when the chart is drilled down to the month level.
3. YTD growth based on the last visible month
Growth % YTD vs LY :=
VAR MaxVisibleDate = MAX('Calendar'[Date])
VAR StartOfYearCurrent = DATE(YEAR(MaxVisibleDate), 1, 1)
VAR CurrentPeriodValue =
CALCULATE(
[Total Value],
DATESBETWEEN('Calendar'[Date], StartOfYearCurrent, MaxVisibleDate)
)
VAR StartOfYearPrev = EDATE(StartOfYearCurrent, -12)
VAR EndPrev = EDATE(MaxVisibleDate, -12)
VAR PrevPeriodValue =
CALCULATE(
[Total Value],
DATESBETWEEN('Calendar'[Date], StartOfYearPrev, EndPrev)
)
RETURN
DIVIDE(CurrentPeriodValue - PrevPeriodValue, PrevPeriodValue)
This compares Jan–SelectedMonth of the current year vs Jan–SelectedMonth of the previous year.
4. A single measure that switches automatically by drill level
Growth % (Auto) :=
VAR MaxVisibleDate = MAX('Calendar'[Date])
VAR StartOfYearCurrent = DATE(YEAR(MaxVisibleDate), 1, 1)
VAR CurrentPeriodValue =
CALCULATE(
[Total Value],
DATESBETWEEN('Calendar'[Date], StartOfYearCurrent, MaxVisibleDate)
)
VAR StartOfYearPrev = EDATE(StartOfYearCurrent, -12)
VAR EndPrev = EDATE(MaxVisibleDate, -12)
VAR PrevPeriodValue =
CALCULATE(
[Total Value],
DATESBETWEEN('Calendar'[Date], StartOfYearPrev, EndPrev)
)
VAR GrowthYTD = DIVIDE(CurrentPeriodValue - PrevPeriodValue, PrevPeriodValue)
VAR CurrentMonthValue = [Total Value]
VAR PrevMonthValue =
CALCULATE(
[Total Value],
SAMEPERIODLASTYEAR('Calendar'[Date])
)
VAR GrowthMonth = DIVIDE(CurrentMonthValue - PrevMonthValue, PrevMonthValue)
RETURN
SWITCH(
TRUE(),
-- Year level
ISINSCOPE('Calendar'[Year]) && NOT ISINSCOPE('Calendar'[Month]), GrowthYTD,
-- Month level
ISINSCOPE('Calendar'[Year]) && ISINSCOPE('Calendar'[Month]), GrowthMonth,
GrowthYTD
)
This gives you:
YTD-based growth when the user is at the year level.
Month-over-month growth when the user drills down to months.
5. Switching between values and percentages
If you want the user to choose whether to see raw numbers or growth %, create a field parameter with:
[Total Value]
Growth % (Auto)
Place the parameter in the Y-axis and use the slicer to switch between values and percentages.
If you need a more accurate solution based on your actual model, please upload a link to a small sample PBIX (dummy data is fine) that reflects the logic, and also attach an image of the expected output.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
