topic Re: Filter values based on second calendar table in DAX Commands and Tips

Filter values based on second calendar table

joshua1990 — Fri, 17 Mar 2023 11:56:00 GMT

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?

Re: Filter values based on second calendar table

tamerj1 — Fri, 17 Mar 2023 12:40:25 GMT

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

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

Re: Filter values based on second calendar table

joshua1990 — Fri, 17 Mar 2023 13:02:12 GMT

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

Re: Filter values based on second calendar table

tamerj1 — Fri, 17 Mar 2023 13:09:53 GMT

joshua1990

Try with MIN instead of MAX

Re: Filter values based on second calendar table

joshua1990 — Fri, 17 Mar 2023 13:18:10 GMT

Still the same issue

Re: Filter values based on second calendar table

tamerj1 — Fri, 17 Mar 2023 13:45:20 GMT

joshua1990

What is the date for A?

Re: Filter values based on second calendar table

joshua1990 — Fri, 17 Mar 2023 13:57:55 GMT

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.

Re: Filter values based on second calendar table

tamerj1 — Fri, 17 Mar 2023 14:54:01 GMT

joshua1990

are you sure that Cycle is a disconnected table?

Re: Filter values based on second calendar table

joshua1990 — Fri, 17 Mar 2023 16:51:10 GMT

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

Re: Filter values based on second calendar table

tamerj1 — Fri, 17 Mar 2023 17:13:26 GMT

joshua1990

First place the measure in the visual then the columns