topic Re: How do I sum index on multiple dates? in DAX Commands and Tips

How do I sum index on multiple dates?

jayjay0306 — Wed, 17 Jan 2024 14:03:06 GMT

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):

This I have managed with the following measure:

Index % = VAR MarkedDateToday= MAX('Calendar'[Marked_Date]) VAR varReportDate = SELECTEDVALUE( '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 ) ) VAR Index_calc=DIVIDE(Sumplus14,Summinus14) RETURN IFERROR(IF(MarkedDateToday,Index_calc,0),BLANK())

BUT - and here is my challange:

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:

Does it make sense?

my problem is: how do I sum the "index %"-calculation for all relevant "marked"-dates on for each customer?

any input or suggestion will be greatly appreciated.

Br,

JayJay0306

Re: How do I sum index on multiple dates?

Anonymous — Thu, 18 Jan 2024 08:49:37 GMT

Hi jayjay0306

I understand your requirement, but according to your description, your data comes from three tables, I am not quite sure about the table structure of your three tables, can you provide me with this information? You can refer to the following link to provide the data: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Or show it as a screenshot or excel. Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax

Re: How do I sum index on multiple dates?

sjoerdvn — Thu, 18 Jan 2024 10:46:08 GMT

untested, but try something like this:

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) )

Re: How do I sum index on multiple dates?

jayjay0306 — Fri, 19 Jan 2024 09:39:15 GMT

Hi Sjoerdvn, it works! brilliant! thank you so much 🙂

and thank you, v-xuxinyi-msft, for getting back.

Have a nice day, both.

br,

Jayjay0306