Reply
Frequent Visitor
Posts: 3
Registered: ‎11-14-2018
Accepted Solution

DAX Days between two dates in different tables with filter

I have a table I'm trying to summarize:

Master table
ID    Worked
1      1/1/2018

2      3/4/2017

3      5/2/2015

4      6/7/2018
....

 


by using my specialized Calendar table:
Date            PostingDay
1/1/2018    False
1/2/2018    True
1/3/2018     True
1/4/2018    True

.....

 

 
I need to add either a column or measure to the master table which will count the days in the Calendar table between master.worked and today, but only where Calendar.PostingDay = TRUE. 

I have a relationship between the master table and calendar table joining Calendar.Date as a one-to-many with master.Worked. 

Still pretty new to DAX so I've muddled myself under CALCULATE, COUNTX, etc and I'm not sure how to get this info.

 

Any ideas?  Thanks in advance


Accepted Solutions
Super User
Posts: 9,834
Registered: ‎07-11-2015

Re: DAX Days between two dates in different tables with filter

I believe you want something along the lines of:

 

 

Measure = 
VAR __worked = MAX('Master'[Worked])
RETURN
COUNTX(FILTER(ALL('Calendar'),[PostingDay] = TRUE() && [Date]<=TODAY() && [Date]>=__worked),[Date])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


View solution in original post


All Replies
Super User
Posts: 9,834
Registered: ‎07-11-2015

Re: DAX Days between two dates in different tables with filter

I believe you want something along the lines of:

 

 

Measure = 
VAR __worked = MAX('Master'[Worked])
RETURN
COUNTX(FILTER(ALL('Calendar'),[PostingDay] = TRUE() && [Date]<=TODAY() && [Date]>=__worked),[Date])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Posts: 3,716
Registered: ‎01-14-2017

Re: DAX Days between two dates in different tables with filter

Hi,

 

Could you share the exact expected layout and result.