Forum Discussion
DAX time comparison function help
Would be grateful for advice on how to calculate the following with a DAX formula & Power Pivot (Excel 2016) I have a sheet with three columns: Date (in DD/MM/YYYY) | Name of Sales Person | Sale ...
First you need to have dates table (as variant could be created within Power Pivot) linked to Sales
DAX measures could be as
Sales 6 months:=VAR endDate=MAX('Calendar'[Date])
VAR startDate=EDATE(endDate,-6)
RETURN
CALCULATE (
SUM ( Table1[Sales] ),
ALL ( 'Calendar' ),
('Calendar'[Date] >= startDate
&& 'Calendar'[Date] <= endDate
))
if in simplest case to define periods manually within measures. For another 6 months
Sales Prev 6 months:=VAR endDate=EDATE(MAX('Calendar'[Date]),-6)
VAR startDate=EDATE(endDate,-13)+1
RETURN
CALCULATE (
SUM ( Table1[Sales] ),
ALL ( 'Calendar' ),
('Calendar'[Date] >= startDate
&& 'Calendar'[Date] <= endDate
))
Please check in attached.
- Sergei, thank you so much for your help. I shoud have clarified that I meant 6 months from present, so I got it working with Today () instead of using the MAX function for endDate in your example. Very helpful.
I'm wondering for a related scenario - if there are duplicate entries and a 4th field for "Tranasaction ID" (e.g. more than one sales person involved in the same sale). Is there a function for the sum of total amount based on distinct Transaction ID only?Above is quite simple mockup to illustrate an approach. You may max of period on TODAY(), when result will be like
Not sure what is Transaction ID in the model, but in general you may aggregate as you need. Useful patterns are here DAX Patterns
