Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago

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?

Need Help

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

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

    Cycle Sales =
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Date'[Date] <= MAX ( Cycle[Date] ) )
)
    • joshua1990's avatar
      joshua1990
      Post Prodigy
      3 years ago

      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