Forum Discussion
Dynamic Time X Axis with hourly data
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)
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 chartPlease see file attach.
7 Replies
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 chartPlease see file attach.
Hi ty for your effort. It looks realy promising.
Eveything works but today doenst. Do you have an idea why today dont works?
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:
- Create a “Time Selection” table with options like Today, Last 7 Days, Last Year, etc.
- 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.
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
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
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
