Forum Discussion

jayjay0306's avatar
jayjay0306
Icon for Helper III rankHelper III
2 years ago
Solved

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: exampl...
"DAX help"
DAX tips
  • sjoerdvn's avatar
    sjoerdvn
    2 years ago

    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)
)
sjoerdvn's avatar
sjoerdvn
Icon for Solution Sage rankSolution Sage
2 years ago

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