Calculate filter context not working at totals

[Dharvi3553]

Hi,

I'm facing a challenge to show Store totals and subsequently Grand totals accurately.

The reason is Dim_Date[DateKey] is just ignoring filter context at individual Promotions[PromoStartDateKey] and Promotions[PromoEndDateKey] date range at promotion level per a store.

Promotion	PromotionStartDate	PromotionEndDate	Store
Back to School	2023-01-01	2023-02-28	Carine
Easter	2023-04-06	2023-04-09	Carine
Easter	2023-04-06	2023-04-09	Rossmoyne

For example Carine store has run 2 promotions.

Measure takes promotion start date as 2023-01-01 and promotion end date as 2023-04-09 at store totals and grand totals which means it fails to consider individual promotion start and end dates per above table.

Store total at Carine should be $2871.51 + $1033.23 = $3904.74. This is incorrectly shown as $7875.02

Similary grand total should be $2871.51 + $1033.23 + $756.56 = $4661.30. This is incorrectly shown as $10560.31

Could you please help me to correct this measure 

Promotion Duration PLU Sales $:= 

CALCULATE(

SUM(Sales),

Dim_Item[PromoFlag] = 1,

TREATAS(VALUES(Promotions[OrganisationItemIdentificationKey]),Dim_Item[OrganisationItemidentificationkey_Promo]), 

TREATAS(VALUES(Promotions[OrganisationKey]),Dim_Item[Organisationkey]), 

FILTER(ALL(Promotions),Promotions[PromotionName]=Max(Instore_Promotions[PromotionName])),

FILTER(ALL(Promotions),Promotions[Store]=Max(Promotions[Store])),

FILTER(ALL(Dim_Date), Dim_Date[DateKey] >= MIN(Promotions[PromoStartDateKey]) &&  Dim_Date[DateKey] <= MAX(Promotions[PromoEndDateKey]))

)

Below is my data model.

Fact_Sales: Sales fact table

DimDate: Date dimension table.

DimOrganisation: Store dimension table.

Dim_Item: Item dimension table. I have added 3 calculated columns which would be used in measures.

OrganisationItemidentificationkey_Promo
=CALCULATE(FIRSTNONBLANK(OrganisationItem_Promo[OrganisationItemidentificationkey],1),
FILTER(ALLNOBLANKROW(OrganisationItem_Promo), OrganisationItem_Promo[OrganisationItemidentificationkey] = Dim_Item[OrganisationItemIdentificationKey]))

PromoFlag
=IF(Dim_Item[OrganisationItemidentificationkey_Promo] <> BLANK(), 1, 0)

PromoName
=CALCULATE(FIRSTNONBLANK(Promotions[PromotionName],1),
FILTER(ALLNOBLANKROW(Promotions), Promotions[OrganisationItemIdentificationKey] = Dim_Item[OrganisationItemidentificationkey_Promo]),
FILTER(ALLNOBLANKROW(Promotions),Promotions[OrganisationKey] = Dim_Item[OrganisationKey]))

Promotions: Table hosting promotions data. OrganisationItemIdentificationKey is unique identifier across all stores.

OrganisationItem_Promo: This table contains distinct Promotion items identified by OrganisationItemidentificationkey i.e. It holds distinct OrganisationItemidentificationkey of Promotions table.

@amitchandak @Ritaf1983 @Greg_Deckler @Ahmedx @lbendlin @Ashish_Mathur 

Thank you in advance!

[Greg_Deckler · Accepted solution]

@Dharvi3553 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[Dharvi3553 · Accepted solution]

Thank you @Greg 

I resolved the issue with below.

Promo PLU Sales $:= CALCULATE([Sales $], TREATAS(VALUES(Promotions[OrganisationItemIdentificationKey]),Dim_Item[OrganisationItemidentificationkey_Promo]), Dim_Item[PromoFlag] = 1);

Promo Duration PLU Sales $:=
CALCULATE(SUMX(VALUES(Promotions[OrganisationItemIdentificationKey]),
CALCULATE(
IF(HASONEVALUE('Promotions'[PLU]),
CALCULATE([Promo PLU Sales $],
FILTER(Promotions,Promotions[Promotion]=Max(Promotions[Promotion])),
FILTER(Promotions,Promotions[Store]=Max(Promotions[Store])),
FILTER(Dim_Date, Dim_Date[DateKey] >= MIN(Promotions[PromoStartDateKey]) && Dim_Date[DateKey] <= MAX(Instore_Promotions[PromoEndDateKey]))

),BLANK())
)
)
)

[Greg_Deckler · Accepted solution]

@Dharvi3553 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8