Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jayjay0306
Helper III
Helper III

How do I sum index on multiple dates?

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:

pic121.PNG

 

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

pic21.PNG

 

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:

pic22.PNG

 

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

1 ACCEPTED SOLUTION
sjoerdvn
Super User
Super User

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

View solution in original post

3 REPLIES 3
jayjay0306
Helper III
Helper III

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

sjoerdvn
Super User
Super User

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)
)
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.