DAX to get totals based on condition of another column

[BI_Prachi]

Hi All,

I need help with Dax measure. Requirement is to get total quantities from Sales table only for those locations which exists in target table and transaction date of Sales should be greater than StartDate in Target Table.

SampleTables :
Targets:

Location	Area	Region	StartDate	Target
L01	A1	R1	05/06/2023	100
L02	A2	R1	07/07/2023	150

DimSite:

SiteKey	Location	Area	Region
S1	L01	A1	R1
S2	L02	A2	R1
S3	L03	A1	R1
S4	L04	A3	R2

  Sales :

SiteKey	TransactionDate	Qty
S1	01/01/2023	40
S1	06/06/2023	30
S1	07/06/2023	40
S2	05/07/2023	60
S2	10/07/2023	50
S3	10/07/2023	40
S4	12/12/2023	70

Expected output is:

Location	Qty
L1	70
L2	50

Relationship between tables are :
DimSite[Sitekey] ---> Sales[Sitekey] (one to many)
DimSite[LocationCode] <----> Target[LocationCode] (one- to-one)
DimDate[Date] -----> Sales[TransactionDate] (One-to-many)

Note :  Sales and dimSite table are being connected in DirectQueryMode as being loaded from exisisting power Bi model which cannot be changed and Target table in import mode which has target for limited locations.

I have created below measure which is giving correct output but taking ages to load on visual. 

CALCULATE(

    SUM(Sales[Quantity]),

    FILTER(

        SUMMARIZE(

            Sales,

            Sales[TransactionDate],

            'dim Site'[SiteKey],

            "GoLiveDate", CALCULATE(Max(StoreTarget[StartDate]), FILTER('dim Site', 'dim Site'[LocationCode]= VALUES(StoreTarget[LocationCode])))

),

        Sales[TransactionDate] >= [GoLiveDate]

    ),

    TREATAS(

        VALUES(StoreTarget[LocationCode]),

        'dim Site'[LocationCode]

    )

)

Thanks in advance!

[v-nuoc-msft]

Hi @BI_Prachi 

Please try the following:

Here's some dummy data

"Targets"

"Sales"

"DimSite"

Create a measure.

Qty = 
VAR TargetLocations = 
    CALCULATETABLE(
        VALUES(Targets[Location]),
        REMOVEFILTERS(DimSite)
    )
RETURN
    CALCULATE(
        SUM(Sales[Qty]),
        Sales[TransactionDate] > MIN(Targets[StartDate]),
        DimSite[Location] IN TargetLocations
    )

Here is the result.

Regards,

Nono Chen

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

[BI_Prachi]

Thanks @v-nuoc-msft 

I tried the solution. It's working fine at location level but totals are still not matching. As transaction date is being compared with min StartDate irrespective of StartDate of respective Location.