cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## How do I filer product on index?

Hi people,

I hope you can help me with a complex DAX problem:

I have a report based on the following tables:

D_Customer

D_Date

F_Sale

Each customer has day-to-day sale in the table:

example:

now, some of the sales dates for each customer are "marked" for various reasons.

On these "marked" dates, I try to do the following:

on each marked date I want to

1) "Date mark - 14 (Sum)" =  sum the sales for 14 days prior to the marked date

2) "Date mark +14 (Sum)" = sum the sales for 14 days after the marked date

3) make an index % =  "Date mark + 14 (Sum)" / "Date mark - 14 (Sum)"

example (illustrated):

4) once I have calculated the "Date mark + 14 (Sum)", "Date mark - 14 (Sum)" and  Index%, I need to make the average Index% for all marked dates on each customer:

example (illustrated):

This I have managed with the following measure:

``````Date-14 (index)avg per cust =
AVERAGEX(
CALCULATETABLE(VALUES('Calendar'[date]), ALLSELECTED('Calendar'[date]), 'Calendar'[Marked_Date]),
VAR varReportDate = CALCULATE(MAX('Calendar'[date]))
VAR Summinus14=
CALCULATE(
SUM( 'Sales'[Sales]),
FILTER(
ALL( 'Calendar'[date] ),
'Calendar'[date] >= varReportDate-14&&'Calendar'[date] < varReportDate
)
)
VAR Sumplus14=
CALCULATE(
SUM( 'Sales'[Sales] ),
FILTER(
ALL( 'Calendar'[date] ),
'Calendar'[date] <= varReportDate+14&&'Calendar'[date] > varReportDate
)
)
RETURN DIVIDE(Sumplus14,Summinus14)
)``````

It works, BUT I also need to filter the measure on specified products from the product-table (‘Product’ [Product Name]).

I have tried the following, but the outcome is “0”, when I slice on a "product name"?:

``````Date-14 (index)avg pr cust =
AVERAGEX(
CALCULATETABLE(VALUES('Calendar'[date]), ALLSELECTED('Calendar'[date]), 'Visits Executed'[Visit_Date]),
VAR varReportDate = CALCULATE(MAX('Calendar'[date]))
VAR varProduct=SELECTEDVALUE('Product'[Product Name])
VAR Summinus14=
CALCULATE(
SUM( 'KPI Sell Out'[Sales Out Packs CY]),
FILTER(
ALL( 'Calendar'[date] ),
'Calendar'[date] >= varReportDate-14&&'Calendar'[date] < varReportDate
), 'Product'[Product Name]=varProduct
)
VAR Sumplus14=
CALCULATE(
SUM( 'KPI Sell Out'[Sales Out Packs CY] ),
FILTER(
ALL( 'Calendar'[date] ),
'Calendar'[date] <= varReportDate+14&&'Calendar'[date] > varReportDate
), 'Product'[Product Name]=varProduct
)
RETURN DIVIDE(Sumplus14,Summinus14)
)``````

How do I add a filter in the measure here, so I only get the sales index % on the product chosen?

any input or suggestion will be greatly appreciated.

Thanks.

Br,

JayJay0306

1 ACCEPTED SOLUTION
Super User

Hi @jayjay0306 without model it is hard to recreate your scenario and debug it.

Still, try v2 below; part FILTER is change

V2 measure=
AVERAGEX(
CALCULATETABLE(
VALUES('Calendar'[date]),
ALLSELECTED('Calendar'[date]), 'Visits Executed'[Visit_Date])
,
VAR varReportDate = CALCULATE(MAX('Calendar'[date]))
VAR varProduct=SELECTEDVALUE('Product'[Product Name])
VAR Summinus14=
CALCULATE(
SUM( 'KPI Sell Out'[Sales Out Packs CY]),
FILTER(
ALL( 'Calendar'[date] ),
'Calendar'[date] >= varReportDate-14&&'Calendar'[date] < varReportDate
), FILTER('Product','Product'[Product Name]=varProduct)
)
VAR Sumplus14=
CALCULATE(
SUM( 'KPI Sell Out'[Sales Out Packs CY] ),
FILTER(
ALL( 'Calendar'[date] ),
'Calendar'[date] <= varReportDate+14&&'Calendar'[date] > varReportDate
), FILTER('Product','Product'[Product Name]=varProduct)
)
RETURN DIVIDE(Sumplus14,Summinus14)

Proud to be a Super User!

4 REPLIES 4
Helper III

thanks sum_bih, It works. Your calculation works perfectly, once I had made some corrections in the datamodel.

Thanks again, It is mus apprecated 🙂

have a nice day.

br,

jayjay0306

Helper III

Thanks Some_bih,

I believe your script works, but I have made some further investigation and think is due to the data model setup.

This is my model (the relevant part, at least):

I believe the cause of the problem is, that the relational flow between the tables?

Below I have simplified the script so it only shows a summary of the last 14 days ("Summinus14") in order to get a better overview.

Also, I have tried to expand the script to get the data flow both ways?

But I still get the result =0 ?

``````V2_2 measure =
VAR VisitDateToday= MAX('Visits Executed'[Visit_Date])
VAR varReportDate = SELECTEDVALUE( 'Calendar'[date] )
VAR Summinus14=
CALCULATE(
SUM( 'Sales'[Sales] ),
FILTER(
ALL( 'Calendar'[date] ),
'Calendar'[date] >= varReportDate-14&&'Calendar'[date] < varReportDate
),
FILTER('Product','Product'[Product Name]=varProduct),
CROSSFILTER('Product'[PRODUCT_KEY],'Sales'[PRODUCT_KEY],Both),
CROSSFILTER('Calendar'[DATE_KEY],'Sales'[DATE_KEY],Both),
CROSSFILTER('Customer'[CUSTOMER_KEY],'Sales'[CUSTOMER_KEY],Both),
CROSSFILTER('Visits Executed'[PRODUCT_KEY],'Product'[PRODUCT_KEY],Both),
CROSSFILTER('Visits Executed'[VISIT_DT_KEY],'Calendar'[DATE_KEY],Both),
CROSSFILTER('Visits Executed'[CUSTOMER_KEY],'Customer'[CUSTOMER_KEY],Both)
)
VAR Index_calc=Summinus14
RETURN
IFERROR(IF(VisitDateToday,Summinus14,0),BLANK())``````

Unfortunately I can't send you the dastamodel, but maybe you can see where it goes wrong?

thanks.

Br,

JayJay0306

Solution Sage

Please share your datamodel and product and customer tables without any sensitive information, just few records. Also please paste table, not image.

Super User

Hi @jayjay0306 without model it is hard to recreate your scenario and debug it.

Still, try v2 below; part FILTER is change

V2 measure=
AVERAGEX(
CALCULATETABLE(
VALUES('Calendar'[date]),
ALLSELECTED('Calendar'[date]), 'Visits Executed'[Visit_Date])
,
VAR varReportDate = CALCULATE(MAX('Calendar'[date]))
VAR varProduct=SELECTEDVALUE('Product'[Product Name])
VAR Summinus14=
CALCULATE(
SUM( 'KPI Sell Out'[Sales Out Packs CY]),
FILTER(
ALL( 'Calendar'[date] ),
'Calendar'[date] >= varReportDate-14&&'Calendar'[date] < varReportDate
), FILTER('Product','Product'[Product Name]=varProduct)
)
VAR Sumplus14=
CALCULATE(
SUM( 'KPI Sell Out'[Sales Out Packs CY] ),
FILTER(
ALL( 'Calendar'[date] ),
'Calendar'[date] <= varReportDate+14&&'Calendar'[date] > varReportDate
), FILTER('Product','Product'[Product Name]=varProduct)
)
RETURN DIVIDE(Sumplus14,Summinus14)

Proud to be a Super User!