max per order issue

[macwhe]

Hi All,

I am struglling with MAX function. I have 2 dimension tables and on Facttable. between 2 dimensions there is a inactive relationship . and between the fact table and FreezeDate Snapshot there is one active relationship

First Dimension

Freeze date SnapShot
1-Sep-21
2-Sep-21

Second

Freeze Comparison Snaphot
31-Aug-21
1-Sep-21

facttable

FreezeDate	0rder	Quantity
1-Sep-21	P1001	10
2-Sep-21	P1001	20
1-Sep-21	P1002	1
2-Sep-21	P1002	10

The goals is to comapre the order qty bewteen those two dates. Lets suppose user selects as Freeze Date snapshot 1s SEP 2021 and Comaprison date is 31 AUG 2021. The results that I need to achieve should be like below . I need to display in visual TABLE.

Freeze date Comparison Snapshot	order Comparison Snapshot	Quanity Comparison Snapshot	Freeze Date Snapshot	Order Snaphot	QTY Snapshot	Qty Difference Between 2 freezes dates
31-Aug-21	empty	Empty	1-Sep-21	P1001	10	-10
31-Aug-21	Empty	Empty	1-Sep-21	P1002	20	-20

However the results that I have is : As you can see it shows the max order which is P1002 .

Freeze date Comparison Snapshot	order Comparison Snapshot	Quanity Comparison Snapshot	Freeze Date Snapshot	Order Snapshot	QTY Snaphot	Qty Difference Between 2 freezes dates
31-Aug-21	empty	Empty	1-Sep-21	P1002	10	-10

But if I drop the "order column "  from the fact table it shows correctly but I dont want to show the order column from Fact Table

Freeze date Comparison Snapshot	order Comparison Snapshot	Quanity Snapshot	Freeze Date Snapshot	Order	QTY	Qty Difference Between 2 freezes dates	"Order"
31-Aug-21	empty	Empty	1-Sep-21	P1001	10	-10	P1001
31-Aug-21	Empty	Empty	1-Sep-21	P1002	20	-20	P1002

these are my measures

Order Comaprison Snaphot=Calculate (
Max(Order).Facttable, Remove filters (FreezeDate Snapshot),
UserRelationship(freezedateSnapshot), (freezedateComparison Snapshot)

Qty Comparison =Calculate (
SUM(Qty).Facttable, Remove filters (FreezeDate Snapshot),
UserRelationship(freezedateSnapshot), (freezedateComparison Snapshot)

Order Snaphot=Max(Order).Facttable

QTY Snapshot=sum(Qty).facttable

[v-xulin-mstf · Accepted solution]

Hi @macwhe,

Try measure as:

Measure = 
IF(
    MAX(Facttable[FreezeDate])>=ALLSELECTED('Second Dimension'[Freeze Comparison Snaphot]) && MAX(Facttable[FreezeDate])<=ALLSELECTED('First Dimension'[Freeze date SnapShot]),
    1,
    0
)

Here is the output:

The demo is attached, please try it.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[lbendlin]

Are these snapshot dates predefined or would it be easier to let the user select the date range via a slicer?

[macwhe]

HI, the dates are selected by user  Below are the dates that I have available in the slicers. Basically  we have a new freeze date everyday depending on the business and at end of the month we also have a freeze. I forgot to mention I am using Direct Query. thanks a lot slicers

[lbendlin]

My question was if you could combine both slicers into one date range slicer.

[macwhe]

 I cannot combine both slicers the date range , I need to have 2 slicers.

Thanks