Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joshua1990
Post Prodigy
Post Prodigy

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 columns in matrix from the first date table.

Jan Feb Mar Apr May June ... ... ... ...
800 900 400 660 405 350 450 ... ... ...

 

Now I would like to add again Months as a row but from the second date table.

THis selected row determines the last date which should be considered for the matrix:

  Jan Feb Mar Apr May June ... ... ... ...
Feb 800 0 0 0 0 0 0 0 0 0
Mar 800 900 0 0 0 0 0 0 0 0
Apr 800 900 400 0 0 0 0 0 0 0

 

How would you do that with DAX?

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1679362153115.png

(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.

vtangjiemsft_3-1679362643666.png

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. 

View solution in original post

4 REPLIES 4
PaulOlding
Solution Sage
Solution Sage

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)

PaulOlding_0-1679302704955.png

 

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)

PaulOlding_1-1679302955827.png

 

 

v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1679362153115.png

(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.

vtangjiemsft_3-1679362643666.png

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. 

joshua1990
Post Prodigy
Post Prodigy

Any ideas? Do you need further information?

joshua1990
Post Prodigy
Post Prodigy

No ideas?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors