Dax - Filter by 2 date columns - Metric

[Ryks]

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

ID	Open	Close
1	15-Jan-21
2	20-Feb-21	20-Mar-21
3	01-Jan-21	20-Mar-21
4	10-Jan-21	28-Apr-21

Answer should be:

Feb-21	4
Mar-21	2
Apr-21	1

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.

[ryan_mayu · Accepted solution]

@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

[Greg_Deckler]

@Ryks 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[Ryks]

Hi @Greg_Deckler 

Great solution. 

I however, have relationships on my table.

Active - Open to Calendar Date and inactive with Close.

[ryan_mayu]

@Ryks 

you can try this

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

pls see the attachment below

[Ryks]

Wow, @ryan.  Amazying!  

Just checking my data set, will get back to you.

Many Thanks

[ryan_mayu]

you are welcome

[Ryks]

Hi @ryan_mayu 

The reason why my data does not work, is that I have a active relationship between calander date and inactive relationship between calander date and close.  I am playing around with

USERELATIONSHIP but have not landed on anything. 

[ryan_mayu · Accepted solution]

@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

[Ryks · Accepted solution]

Excellent.

Thank you @ryan_mayu 

[ryan_mayu]

you are welcome