Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost 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 ... ... ... ... ... ... ......
Need Help
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity 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
Icon for Post Prodigy rankPost 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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    joshua1990 

    Try with MIN instead of MAX

    • joshua1990's avatar
      joshua1990
      Icon for Post Prodigy rankPost Prodigy
      3 years ago

      Still the same issue

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        joshua1990 

        What is the date for A?