topic Mix% (Dynamic date filters, NaturalInnerjoin, Summarize) in DAX Commands and Tips

Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

barkha — Wed, 12 Nov 2025 17:35:20 GMT

Input Sample Data:

Customer	SKU	Date	Rank	Quantity
A	S100	Jan-24	1	200
A	S100	Oct-24	2	400
A	S200	Nov-24	3	45
A	S100	Mar-25	4	100
A	S100	Oct-25	5	200
A	S200	Oct-25	5	67
B	S100	Jan-24	1	214
B	S100	Oct-24	2	412
B	S100	Mar-25	4	112
B	S100	Oct-25	5	211
B	S200	Oct-25	5	78
B	S300	Oct-25	5	55

Output Sample Data:

Customer	SKU	CY 2025	CY Mix %
A	S100	300	82%
A	S200	67	18%
B	S100	323	100%

Explanantion of Output Table:
1. Quantity is aggregated for Customer and SKU for current year and previous year (CY) vs (PY) using input table.
2. For every customer, only those SKUs are retained which are sold in both years (Naturalinnerjoin). Therefore, SKU S200 & S300 are not considered in output table as they are not present in both CY and PY.
3. Calculate Mix% for current year, within in each customer by dividing SKU level quantity by Customer level total quantity.
4. Rank column is according to date. The rank column from input table is used to filter time period dynamically.

5. I am not able to achieve total at customer level after Naturalinnerjoin in DAX.

DAX:

Brief explanation of DAX -
1. Data is filtered first time using Current time period: Dynamic filtering using current start date, previous start date and last-n-months slicers. I have used ranks to filter time periods below. Actual & Base calendars are not connected to sales table.
2. Same data filtered using Base time period.
3. Both of the datasets are inner joined at Customer x SKU level to find common combinations (Naturalinnerjoin). All_CustomerIDs, All_SKUCode are connected to sales table.
4. This table with common combinations is again summarized at customer level to find customer level total.
5. Table from point 4 is joined back on table in point 3 (inner-joined).

6. Calculate mix% by sku level qty/ customer level qty.
7. Return mix% using averagex for each Customer and SKU

Issues in DAX:
1. The calculation fails at point 4. i.e. not able to calculate customer level totals. Please suggest correct DAX.
2. Is return method correct?
3. Can this be optimized? any better method to achieve this type of calculation?

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

Greg_Deckler — Wed, 12 Nov 2025 16:26:35 GMT

barkha Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

barkha — Wed, 12 Nov 2025 17:17:12 GMT

Greg_Deckler
Thanks for the response.
I have pasted a small sample input data and output. Kindly let me know if the issue is not clear.
Looking forward to your response.

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

Praful_Potphode — Thu, 13 Nov 2025 11:50:31 GMT

Hi barkha ,

try changing step 4 to below:

VAR CustomerTotals = ADDCOLUMNS( SUMMARIZE(FullJoinTable, All_CustomerIDs[CustomerNo]), "TotalBaseQuantity", VAR CurrentCustomer = [CustomerNo] RETURN SUMX( FILTER(FullJoinTable, [CustomerNo] = CurrentCustomer), [QuantityB] ), "TotalActualQuantity", VAR CurrentCustomer = [CustomerNo] RETURN SUMX( FILTER(FullJoinTable, [CustomerNo] = CurrentCustomer), [QuantityA] ) )

please try and let me know.

Also try summarizecolumns or groupby instead of summarize dax as it is usually recommended.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

barkha — Thu, 13 Nov 2025 13:50:52 GMT

Thanks for your response.
The solution is not working. I have tried summarizecolumns / groupby and other methods as well, not working.

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

Praful_Potphode — Thu, 13 Nov 2025 14:54:21 GMT

Hi barkha ,

can you share the data model snapshot to understand it better.

Thanks and Regards,

Praful

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

v-echaithra — Wed, 10 Dec 2025 12:20:22 GMT

Hi Praful_Potphode ,

Please refer to the pbix below attatctched, and refer to the power query steps to get your desired output.

Hope this helps.
Thank you.

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

v-echaithra — Tue, 16 Dec 2025 07:55:05 GMT

Hi barkha ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

v-echaithra — Mon, 22 Dec 2025 10:50:59 GMT

Hi barkha ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

tamerj1 — Mon, 22 Dec 2025 20:19:42 GMT

Hi barkha

I suggest to utilize UDF's to easily store the tables in this measure as I guess same tables will be used in multiple measures.
Your approach is %100 correct but I believe some steps require little more attention.
I didn't have the chance to implement and test the following solution but I hope you can do from your end.

MnA = VAR base_rank = SELECTEDVALUE ( BaseCalendar[Rank] ) VAR actual_rank = SELECTEDVALUE ( ActualCalendar[Rank] ) VAR timePeriod = SELECTEDVALUE ( TimePeriod[SelectedPeriod] ) - 1 // 1. VAR tbl1 = SUMMARIZE ( FILTER ( CleanSales, CleanSales[rank] >= base_rank - timePeriod && CleanSales[rank] <= base_rank ), All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode], "QuantityB", SUM ( CleanSales[Quantity] ) ) // 2. VAR tbl2 = SUMMARIZE ( FILTER ( CleanSales, CleanSales[rank] >= actual_rank - timePeriod && CleanSales[rank] <= actual_rank ), All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode], "QuantityA", SUM ( CleanSales[Quantity] ) ) // 3. VAR InnerJoinTable1 = NATURALINNERJOIN ( tbl1, tbl2 ) // 4. VAR tbl3 = CALCULATETABLE ( SUMMARIZE ( FILTER ( CleanSales, CleanSales[rank] >= base_rank - timePeriod && CleanSales[rank] <= base_rank ), All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode], "QuantityB", SUM ( CleanSales[Quantity] ) ), ALL ( All_SKUCode[SKUCode] ) ) // 5. VAR tbl4 = CALCULATETABLE ( SUMMARIZE ( FILTER ( CleanSales, CleanSales[rank] >= actual_rank - timePeriod && CleanSales[rank] <= actual_rank ), All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode], "QuantityA", SUM ( CleanSales[Quantity] ) ), ALL ( All_SKUCode[SKUCode] ) ) // 6. VAR InnerJoinTable2 = NATURALINNERJOIN ( tbl3, tbl4 ) // 7. VAR CustomerTotals = GROUPBY ( InnerJoinTable2, All_CustomerIDs[CustomerNo], "TotalBaseQuantity", SUMX ( CURRENTGROUP (), [QuantityB] ), "TotalActualQuantity", SUMX ( CURRENTGROUP (), [QuantityA] ) ) // 8. Merge totals back VAR FullWithTotals = NATURALLEFTOUTERJOIN ( InnerJoinTable1, CustomerTotals ) // 9. VAR MAMB = ADDCOLUMNS ( FullWithTotals, "Mn_A", DIVIDE ( [QuantityA], [TotalActualQuantity], 0 ), "Mn_B", DIVIDE ( [QuantityB], [TotalBaseQuantity], 0 ) ) // 10. // Final calculation: Return the sum of Mn_B across all products in tbl1 RETURN AVERAGEX ( MAMB, [Mn_A] )

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

tamerj1 — Mon, 22 Dec 2025 20:26:30 GMT

barkha

I'm extremely sorry! Looks like I just marked my solution as acceptable solution by mistake and I don't know how to undo that! Maybe v-echaithra can help!

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

v-echaithra — Tue, 23 Dec 2025 10:17:31 GMT

Hi tamerj1 ,

No worries, you can easily undo it.

Please click on the three dots (⋯) on the reply that was marked as the solution. You should see an option called “Not the solution”. Selecting that will unmark the reply as the accepted solution.

Hope that helps!

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

tamerj1 — Wed, 24 Dec 2025 11:07:49 GMT

barkha
Ok, I had the chance to implement the solution in the attached sample file.

QAQB = () => VAR base_rank = SELECTEDVALUE ( BaseCalendar[Rank] ) VAR actual_rank = SELECTEDVALUE ( ActualCalendar[Rank] ) VAR timePeriod = SELECTEDVALUE ( TimePeriod[SelectedPeriod] ) - 1 // 1. VAR tbl1 = SUMMARIZE ( FILTER ( CleanSales, CleanSales[rank] >= base_rank - timePeriod && CleanSales[rank] <= base_rank ), All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode], "QuantityB", SUM ( CleanSales[Quantity] ) ) // 2. VAR tbl2 = SUMMARIZE ( FILTER ( CleanSales, CleanSales[rank] >= actual_rank - timePeriod && CleanSales[rank] <= actual_rank ), All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode], "QuantityA", SUM ( CleanSales[Quantity] ) ) // 3. VAR InnerJoinTable1 = NATURALINNERJOIN ( tbl1, tbl2 ) RETURN InnerJoinTable1

MAMB = () => VAR base_rank = SELECTEDVALUE ( BaseCalendar[Rank] ) VAR actual_rank = SELECTEDVALUE ( ActualCalendar[Rank] ) VAR timePeriod = SELECTEDVALUE ( TimePeriod[SelectedPeriod] ) - 1 // 4. VAR tbl3 = CALCULATETABLE ( SUMMARIZE ( FILTER ( CleanSales, CleanSales[rank] >= base_rank - timePeriod && CleanSales[rank] <= base_rank ), All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode], "QuantityB", SUM ( CleanSales[Quantity] ) ), ALL ( All_SKUCode[SKUCode] ) ) // 5. VAR tbl4 = CALCULATETABLE ( SUMMARIZE ( FILTER ( CleanSales, CleanSales[rank] >= actual_rank - timePeriod && CleanSales[rank] <= actual_rank ), All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode], "QuantityA", SUM ( CleanSales[Quantity] ) ), ALL ( All_SKUCode[SKUCode] ) ) // 6. VAR InnerJoinTable2 = NATURALINNERJOIN ( tbl3, tbl4 ) // 7. VAR CustomerTotals = GROUPBY ( InnerJoinTable2, All_CustomerIDs[CustomerNo], "TotalBaseQuantity", SUMX ( CURRENTGROUP (), [QuantityB] ), "TotalActualQuantity", SUMX ( CURRENTGROUP (), [QuantityA] ) ) // 8. Merge totals back VAR FullWithTotals = NATURALLEFTOUTERJOIN ( QAQB(), CustomerTotals ) // 9. VAR MAMB = ADDCOLUMNS ( FullWithTotals, "Mn_A", DIVIDE ( [QuantityA], [TotalActualQuantity], 0 ), "Mn_B", DIVIDE ( [QuantityB], [TotalBaseQuantity], 0 ) ) RETURN MAMB

CY Quantity = SUMX ( QAQB(), [QuantityB])

CY Mix = AVERAGEX ( MAMB(), [Mn_B] )

PY Quantity = SUMX ( QAQB(), [QuantityA] )

PY Mix = AVERAGEX ( MAMB(), [Mn_A] )

Re: Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

Rufyda — Thu, 25 Dec 2025 13:58:17 GMT

You can calculate customer-level totals after the NATURALINNERJOIN using ADDCOLUMNS with SUMX(FILTER()) per customer, then merge back with the joined table. This ensures SKU-level Mix% divides correctly by the customer total. Using SUMMARIZECOLUMNS instead of SUMMARIZE can also simplify and optimize the calculation.

Try these solutions and let me know if you need further clarification

Regards,
Rufyda Rahma | MIE