Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi
I have a measure:
mr_average_number_of_orders_per_client_ep_rolling_1year =
CALCULATE(
[mr_average_number_of_orders_per_client_ep],
DATESINPERIOD(
a_confirm_date[confirm_date].[date],
MAX( a_confirm_date[confirm_date].[date]),
-365,
DAY
)
)
The maximum confirm_date in my dataset is today (21.01.2023) but on the chart the values of this measure is shown also beyond this maximum date (almost one year forward - the last value shown is for 31.12.2023). Why? (sorry for Polish names of months on the chart on the chart). Could you please help with this? Thank you.
Solved! Go to Solution.
Okay, I think I got the issue now.
E.g. Here I created a table with dates from today to one year ago:
When I remove the .[Date] from the dax the result is as expected:
Proud to be a Super User!
Hi,
Are you using values from calendar table in your visual? Based on the title of your chart it seems you are using Year, Month, Day hierarchy. If this is the case check if your relationships are functioning.
E.g. Here I have a similar measure:
The main difference is that I use calendar table in my dax:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!
Both in my DAX and in the visualization I use confirm_date from calendar table. The maximum confirm_date both in facts table and in calendar table is 21.01.2023. There is an active relationships between calendar table and facts table.
Okay, I think I got the issue now.
E.g. Here I created a table with dates from today to one year ago:
When I remove the .[Date] from the dax the result is as expected:
Proud to be a Super User!
Ok, I worked it out but I don't yet understand why it works like this. When I removed .[Date] only from the MAX part and left it in the DATESINPERIOD part it worked as I expected. Thank you for help
mr_average_number_of_orders_per_client_ep_rolling_1year =
CALCULATE(
[mr_average_number_of_orders_per_client_ep],
DATESINPERIOD(
a_confirm_date[confirm_date].[date],
MAX( a_confirm_date[confirm_date]),
-365,
DAY
)
)
Thanks for helping. In my opinion however the result after removing .[Date] form the DAX is not as expected. I would expect the first chart you shared to end on todays date as on the screen below - withou the part crossed out (in red).
Okay, I think I got the issue now.
E.g. Here I created a table with dates from today to one year ago:
When I remove the .[Date] from the dax the result is as expected:
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
22 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
31 | |
23 | |
22 | |
22 |