cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Post Prodigy

## Filter values based on second calendar table

Hi experts!

I have a sales table linked to a calendar table.

This values are displayed in a matrix in this format:

 2023-Jan 2023-Feb 2023-Mar ... ... ... ... ... ... ... 500 600 500 700 600 700 800 900 450 200

Then I have a second calendar table that represents specific cycles:

 Date Cycle 01.01.2023 A .... .... 01.12.2023 L

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:

 Cycle 2023-Jan 2023-Feb 2023-Mar ... ... ... ... ... ... ... A 0 0 0 0 0 0 0 0 0 0 B 500 0 0 0 0 0 0 0 0 0 C 500 600 0 0 0 0 0 0 0 0

How would you do that?

9 REPLIES 9
Super User

Hi @joshua1990
Assuming 'Cycle' table is a disconnected table, please try

``````Cycle Sales =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Date'[Date] <= MAX ( Cycle[Date] ) )
)``````
Post Prodigy

Hi, this is not working. This is the result:

 Cycle 2023-Jan 2023-Feb 2023-Mar ... ... ... ... ... ... ... A 0 0 0 0 0 0 0 0 0 0 B 500 0 0 0 0 0 0 0 0 0 C 0 600 0 0 0 0 0 0 0 0

As you can see for cycle "C" I get 0 for 2023-Jan. But there should be a value of 500

Super User

Try with MIN instead of MAX

Post Prodigy

Still the same issue

Super User

What is the date for A?

Post Prodigy

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.

Super User

are you sure that Cycle is a disconnected table?

Post Prodigy

@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

Super User

First place the measure in the visual then the columns

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors