daynamic X over X measure with trend Axis

[radissoufan]

I have a problem doing dynamic measure with axis

if I have a slicers for year, Quarter, Month and day
and I have a KPI with date to date differeance... so what ever will be selected it will dynamicly change the kpi
if years choosen 2024 it will calculate 2024 vs 2023
if quarter choosen q1/2024 it will calculate q1/2024 vs q1/2023
if month choosen m1/2024 it will calculate m1/2024 vs m1/2023
if quarter choosen d1/m1/2024 it will calculate d1/m1/2024 vs d1/m1/2023

any clue how to do it!

[Ashish_Mathur]

Hi,

Ensure you have a Calendar table with calculated column formulas of Year, Month name, Quarter and Month number.  Sort the Month name column by the Month number.  To your slicer, drag any date dimension from the Calendar Table.  Write these measures

Total = sum(Data[Amount])

Total in SPLY = calculate([Total],sameperiodlastyear(calendar[date]))

Hope this helps.

[rajendraongole1]

Hi @radissoufan -  A dynamic measure that compares selected time periods (Year, Quarter, Month, Day) in Power BI using a slicer and dynamically calculates the KPI based on date-to-date differences

you have a properly structured date table that includes columns for Year, Quarter, Month, and Day.

reference solved thread:

Dynamic change in X Axis - Microsoft Fabric Community

Measure:

you can replace [Total Sales] with your actual KPI measure.

Dynamic KPI =
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR SelectedQuarter = SELECTEDVALUE('DateTable'[Quarter])
VAR SelectedMonth = SELECTEDVALUE('DateTable'[Month])
VAR SelectedDay = SELECTEDVALUE('DateTable'[Day])

VAR CurrentPeriodSales =
CALCULATE(
[Total Sales],
DATESBETWEEN('DateTable'[Date], MIN('DateTable'[Date]), MAX('DateTable'[Date]))
)

VAR PreviousYearSales =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('DateTable'[Date])
)

VAR PreviousQuarterSales =
CALCULATE(
[Total Sales],
PREVIOUSQUARTER('DateTable'[Date])
)

VAR PreviousMonthSales =
CALCULATE(
[Total Sales],
PREVIOUSMONTH('DateTable'[Date])
)

VAR PreviousDaySales =
CALCULATE(
[Total Sales],
PREVIOUSDAY('DateTable'[Date])
)

RETURN
IF(
NOT ISBLANK(SelectedDay),
DIVIDE(CurrentPeriodSales - PreviousDaySales, PreviousDaySales, 0),
IF(
NOT ISBLANK(SelectedMonth),
DIVIDE(CurrentPeriodSales - PreviousMonthSales, PreviousMonthSales, 0),
IF(
NOT ISBLANK(SelectedQuarter),
DIVIDE(CurrentPeriodSales - PreviousQuarterSales, PreviousQuarterSales, 0),
IF(
NOT ISBLANK(SelectedYear),
DIVIDE(CurrentPeriodSales - PreviousYearSales, PreviousYearSales, 0),
BLANK()
)
)
)
)

Hope the above calculation helps.