Get help building a comparison measure

[Anonymous]

Hello all,

I want to compare sales included in Current Quarter depending on the Closed or Open date, see the table below

So with this measure the goal is to calculate the maturity of each sale or how much time each sale takes to be closed if it's included on the Quarter. Any help would be really appreciated, thanks

[Anonymous · Accepted solution]

Hi @Anonymous ，

Please correct me if I wrongly understood your question.

Firstly, matrix does not support sub header, if you want to return the datediff in matrix visual, that is not feasible .

My suggestion is to create 4 measures to return the datediff in table visual .

(1)I create two tables, ‘duration data’ and ‘Qty’, like this

(2)Use IF dax to create 4 measures to return the datediff .

1Q = IF(MAX('duration date'[Closed date])<MAX(Qty[1Q]) || MAX('duration date'[Open date])>MAX(Qty[1Q]),"Exclude",DATEDIFF(MAX('duration date'[Open date]),MAX(Qty[1Q]),DAY))

2Q = IF(MAX('duration date'[Closed date])<MAX(Qty[2Q]) || MAX('duration date'[Open date])>MAX(Qty[2Q]),"Exclude",DATEDIFF(MAX('duration date'[Open date]),MAX(Qty[2Q]),DAY))

3Q = IF(MAX('duration date'[Closed date])<MAX(Qty[3Q]) || MAX('duration date'[Open date])>MAX(Qty[3Q]),"Exclude",DATEDIFF(MAX('duration date'[Open date]),MAX(Qty[3Q]),DAY))

4Q = IF(MAX('duration date'[Closed date])<MAX(Qty[4Q]) || MAX('duration date'[Open date])>MAX(Qty[4Q]),"Exclude",DATEDIFF(MAX('duration date'[Open date]),MAX(Qty[4Q]),DAY))

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

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

[Anonymous]

Thank you sooo much, this totally worked 🙂

[amitchandak]

@Anonymous , not very clear, Refer this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

And the file attached for Open items between date and comparison

[Anonymous]

Thanks so much, however I need to exclude whenever:

"Open Date" is < "Reference Date"

or

"Closed Date" >"Reference Date"

Your input is much appreciated 

Thanks