DAX for calculation where difference between two dates is n days

Question

Hi,

How to formulate a DAX expression such as calculate aggregate such as COUNT or SUM where difference between two dates is always 'N' days?

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 1

Hi Raunak

Can you post some sample data?

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

Thnaks Javier.

I am working on there lines:

=calculate(sum(totalsales), filter(fact_table, datecol1-datecol2 = 5))

datecol1 and datecol2 are columns in fact table. I think this should work?

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 3

Hi Raunak

If you want to calculate the expression at the same level of granularity of the fact table, then you could use this as a measure:

SUMX( 
	FactResellerSales, 
	IF( 
		INT( FactResellerSales[DueDate] - FactResellerSales[ShipDate]  ) = 5 , 
		CALCULATE( SUM( FactResellerSales[SalesAmount] ) ) 
	) 
)

---

Javier Guillen
http://javierguillen.wordpress.com/