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
Hello to the forum,
I have a question.
I have the Orders and Calendar tables in Power BI Desktop. Both are linked by a 1-n relationship:
In the Orders table I have the following data:
I want to generate the following report:
In a slicer, the user selects a specific date. In a further slicer, he can then set how many days he would like to look into the past, starting from the selected date. (Here I use a numeric parameter.)
Here is an example:
If the user selects 04/10/2023 and wants to look 7 days into the past, then the line chart should show:
In the line chart, the days 03.04. – 04/10/2023 are displayed.
(In the example, I filtered the diagram by hand.)
Does anyone have an idea how I can implement this?
Greetings
Solved! Go to Solution.
@Jan_Trummel Try something below. Note, assumes disconnected tables for slicers. PBIX is attached below signature.
Measure =
VAR __Date = MAX('Dates'[Date])
VAR __DaysBack = [Parameter Value]
VAR __OrderDate = MAX('Orders'[Order date])
VAR __Result = IF(__OrderDate <= __Date && __OrderDate >= __Date - __DaysBack, SUM('Orders'[Sales]), BLANK())
RETURN
__Result
@Jan_Trummel Try something below. Note, assumes disconnected tables for slicers. PBIX is attached below signature.
Measure =
VAR __Date = MAX('Dates'[Date])
VAR __DaysBack = [Parameter Value]
VAR __OrderDate = MAX('Orders'[Order date])
VAR __Result = IF(__OrderDate <= __Date && __OrderDate >= __Date - __DaysBack, SUM('Orders'[Sales]), BLANK())
RETURN
__Result
Hi, @Greg_Deckler do you think there is a solution where the Calendar table can stay connected to the Orders table?
This would be exactley what I need.
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 |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |