Forum Discussion

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

FILTER on second date table

Hi community! I have a sales table and two date tables. The sales table is linked via Date column to both tables. THen I have a matrix with these sales as a value. Then I have Months selected as ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi joshua1990 ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2)My model relationship is as follows.

    (3) We can create two measures. 

    Measure = 
var _a = IF(SELECTEDVALUE('CALENDAR Table 2'[Month])>SELECTEDVALUE('CALENDAR Table 1'[Month]) ,
CALCULATE(
    SUM('sale Table'[sales]),
    'CALENDAR Table 2'[Date] < MAX('CALENDAR Table 2'[Date])
),0)
return IF(SELECTEDVALUE('CALENDAR Table 2'[Month]) =1,BLANK(),_a)

     

     

    (4) Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

PaulOlding's avatar
PaulOlding
Icon for Solution Sage rankSolution Sage
3 years ago

Hi joshua1990 

I've called the 2 date tables DateC (for the one to go on columns) and DateR (for the one to go on rows)

 

When you create the matrix the filter coming from the column is fine as it is.  Your first table is getting you the correct figures per month.  The issue is the filter coming from the DateR table, so that's what we want to alter.

So, this measure alters that filter:

Sales Triangle = 
CALCULATE(
    SUM(Sales[Amount]),
    DateR[Date] < MIN(DateR[Date])
)

 

and gives you this result (my base data is different, hence the different amounts)