Forum Discussion
Get help building a comparison measure
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
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.
4 Replies
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.
- amitchandak
Super User
Anonymous , not very clear, Refer this blog
And the file attached for Open items between date and comparison
