Forum Discussion

Ryks's avatar
Ryks
Frequent Visitor
5 years ago
Solved

Dax - Filter by 2 date columns - Metric

Hi

 

I hope somebody can help me.

Question:

How many order were open per month.

I have a date table - Calendar(Date) that has an active relationship with Open.  

 

Data set

IDOpenClose
115-Jan-21 
220-Feb-2120-Mar-21
301-Jan-2120-Mar-21
410-Jan-2128-Apr-21

 

Answer should be:

Feb-214
Mar-212
Apr-211

 

I tried countrows and filter, but cannot get it to work.

 

This does not work:

Open = Calculate(CountA('Position Open'[ID]),Filter('Position Open',(Month(‘Position Open’[Open])<=Calendar(Month) && Year(Position Open’[Open])<=Calendar(Year) && isempty('Position Open'[Close]) ||  Month('Position Open'[Close]) > Calendar(Month) && Year(Position Open’[Position Open Date])>Calendar(Year))

 

Thanks for any suggestions.

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Ryks 

    you can modify the measure by adding ALL function

    Measure = 
CALCULATE(COUNTROWS('Table'),FILTER(all('Table'),('Table'[Close]>max('Date'[Date])||ISBLANK('Table'[Close]))&&'Table'[Open]<max('Date'[Date])))

    pls see the attachment below

     

9 Replies