I have a sales table linked to a calendar table.
This values are displayed in a matrix in this format:
Then I have a second calendar table that represents specific cycles:
This table represents the last date that should be considered to be accounted for the matrix above.
If I select Cycle A, then I want to see all sales before that day. If it is "L" etc.
In the end I want something like this:
How would you do that?
Assuming 'Cycle' table is a disconnected table, please try
Cycle Sales = CALCULATE ( [Sales Amount], KEEPFILTERS ( 'Date'[Date] <= MAX ( Cycle[Date] ) ) )
Hi, this is not working. This is the result:
As you can see for cycle "C" I get 0 for 2023-Jan. But there should be a value of 500
Let's take C as an example. Here the date is 01.03.2023.
So in the matrix I would get an value for 2023-Jan and 2023-Feb since both months are before the selected C cycle.
@tamerj1 : Now I tested you approach in a new PBI File and I get this failure:
cannot display the data because power bi can't determine the relationship between two or more fields
Yes, I just have a relation between Date and Sales table. Cycle Table has no relationship to the other tables
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.