Dynamic Time X Axis with hourly data

[showy]

Hello i have the follow problem
i have a simple data model (report here ->https://drive.google.com/file/d/1Ud2ZIeDvxvbrlQ2GjieEyHlXGpYSJ3kz/view?usp=sharing)

My Facts are on hourly base.
I have some predefined time selections for filters.

I want to achive the follow behavior

Heute (Today) -> X Axis should show the time of today with the values
Last 7 days -> X Axis should aggregate to one value on daily base
Last calendar week ->X Axis sholud aggregate to one value on daily base
Last 31 days -> X Axis sholud aggregate to one value on daily base
Last calendar month ->X Axis should aggregate to one value on daily base
Last Year -> X Axis should aggregate to one value on monthly base

I dont want to use bookmarks or seperate field parameters.
I saw a solution with calculation group but i cant find it anymore. Inside there i could choose between the orignal values (hourly base) and aggregated values (depends on selection of the timeperiod slicer)

[MFelix · Accepted solution]

Hi @showy ,

Create a field parameter table with the columns you need for aggregation this will be something similar to this:

Parameter = {
    ("BIS", NAMEOF('F_Energieverbrauch'[BIS]), 0, 1),
    ("Date", NAMEOF('F_Energieverbrauch'[Datum]), 1, 2),
    ("Date", NAMEOF('F_Energieverbrauch'[Datum]), 1, 3),
    ("Date", NAMEOF('F_Energieverbrauch'[Datum]), 1, 4),
    ("Date", NAMEOF('F_Energieverbrauch'[Datum]), 1, 5),
    ("Date", NAMEOF('F_Energieverbrauch'[mONTH]), 1, 6)
}

Now create a relationship one to many between the parameter table and the Datumfilter table now you can have the aggregation has needed.

I have also created a month column for the last aggregation that you need at year level.
Check Bottom chart

Please see file attach.

[v-pgoloju]

Hi @showy,

Just following up to see if the Response provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @showy,

Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @showy,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @MFelix and @Shahid12523  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,

Prasanna Kumar

[Shahid12523]

You can achieve a dynamic X-axis in Power BI using a Calculation Group (via Tabular Editor) or a DAX measure that switches aggregation based on a slicer:

1. Create a “Time Selection” table with options like Today, Last 7 Days, Last Year, etc.
2. Create a measure (DynamicSales) that aggregates depending on selection:

DynamicSales :=
SWITCH(
TRUE(),
SELECTEDVALUE('Time Selection'[Time Selection]) = "Today", [SalesAmount] (hourly),
SELECTEDVALUE('Time Selection'[Time Selection]) IN {"Last 7 Days","Last Calendar Week","Last 31 Days","Last Month"}, CALCULATE([SalesAmount], 'Date'[Date]),
SELECTEDVALUE('Time Selection'[Time Selection]) = "Last Year", CALCULATE([SalesAmount], 'Date'[YearMonth])
)

    3.Use the full date column on X-axis; the measure dynamically aggregates to hourly, daily, or monthly based on             selection.

No bookmarks or multiple fields needed.

[MFelix · Accepted solution]

Hi @showy ,

Create a field parameter table with the columns you need for aggregation this will be something similar to this:

Parameter = {
    ("BIS", NAMEOF('F_Energieverbrauch'[BIS]), 0, 1),
    ("Date", NAMEOF('F_Energieverbrauch'[Datum]), 1, 2),
    ("Date", NAMEOF('F_Energieverbrauch'[Datum]), 1, 3),
    ("Date", NAMEOF('F_Energieverbrauch'[Datum]), 1, 4),
    ("Date", NAMEOF('F_Energieverbrauch'[Datum]), 1, 5),
    ("Date", NAMEOF('F_Energieverbrauch'[mONTH]), 1, 6)
}

Now create a relationship one to many between the parameter table and the Datumfilter table now you can have the aggregation has needed.

I have also created a month column for the last aggregation that you need at year level.
Check Bottom chart

Please see file attach.

[showy]

Hi ty for your effort. It looks realy promising.
Eveything works but today doenst. Do you have an idea why today dont works?

[MFelix · Accepted solution]

Hi @showy ,

The today is not working because you only have dates in the specific report you have sent until the 26 of August today is 27th so since your metric is getting the 0 days compared with today there is no data: