DAX measure not returning expected result

[Leesanity]

Hi Fabric Community,

Can you help me with DAX below? The DAX for a measure but didn't return the expected result.

Goal result:  How many overdeliveries happened across the stores in a week? I am trying to get a table like below example

	Overdeliveries times
Week 1
Store 1	2
Store 2	2
Store 3	1
Total	5

Data:
1) Fact table: carton level information including destination store, promised delivery date & delivered date.
2) Dimension table: destination store and max capacity
3) Rolling calendar: 1 active relationship on promised delivery date & 1 inactive relationship on delivered date

DAX: 

Count of overdeliveries = 
VAR Daily_summary =
    'Fact table',
    'Fact table'[Destination store],
    'Fact table'[Delivered date],
    "Delivered qty", DISTINCTCOUNT('Fact table'[Carton ID]),
    "Max Rece Capa", MAX('Dimension table'[Rec. Cap.])
    ),
    "Delivered qty vs Max Rece Capa", IF([Delivered qty]>[Max Rece Capa],1,0),
    "Week Number", WEEKNUM( 'Fact table'[Delivered date],2)
    )

VAR Weekly_summary =
Groupby(
    Daily_summary,
    'Fact table'[Destination store],
    [Week Number],
    "Overdelivery",SUMX(CURRENTGROUP(),[Delivered qty vs Max Rece Capa]
        ))

Return 
CALCULATE(
    sumx(Weekly_summary, [Overdelivery]),
    USERELATIONSHIP('Rolling Calendar'[Date],'Fact table'[Delivered date]
    )
)   

However, this matrix didn't return right outcome. I created a table using DAX to return Week_summary to check the result accuracy. Then I found that there should be 6 times when the delivered qty exceed max capacity (Store 1, 2, 3 & 4 following the sequence) on Week 7.

but the matrix only shows 2 for Store 1. 

Is this something you can help me to understand why there is such a discrepency? Thanks in advance.

Cheers,

Leesanity

[Leesanity]

Hi @amitchandak ,

Thanks for the solution!

I tried it but it seems not working. The M1 is returning total sum of carton but not cartons delivered to store.

Is there any thing wrong with my DAX solution? I am thinking if it could be the modelling: the matrix is using the "Rolling_calendar"[Week Number] and  "Dimension table"[Destination Store]. The Dimension table has a 1-to-many relationship with the fact table . Thanks!

Cheers,
Tian 

[amitchandak]

@Leesanity , Try two measures like

M1= CALCULATE(DISTINCTCOUNT('Fact table'[Carton ID]),USERELATIONSHIP('Rolling Calendar'[Date],'Fact table'[Delivered date]))

Sumx(SUMMARIZE( 'Fact table'[Destination store],
[Week Number], "Delivered qty", [M1], "Max Rece Capa", MAX('Dimension table'[Rec. Cap.])), IF([Delivered qty]>[Max Rece Capa],1,0))