Forum Discussion
help with getting total between two dates
I am having issues getting the right dax measure to find the cummulative total between two dates in the past. the tricky part is i need to go back a 6mth period and then get the total of the previous...
Please try this approach instead. I've never actually tried this, but it just might work. I thought it might unintentionally include the mindate in the date range, so I filtered that out. Please let me know if it works. If not, there is always the brute force approach using DATE(Year( ), Month(), Day(), but that is less elegant.
6-6 Gross Charges =
VAR mindate =
MIN ( 'Calendar'[Date] )
VAR prev12mos =
DATESINPERIOD ( 'Calendar'[Date], mindate, -12, MONTH )
VAR prev6mos =
DATESINPERIOD ( 'Calendar'[Date], mindate, -6, MONTH )
VAR prevprev6mos =
FILTER ( EXCEPT ( prev12mos, prev6mos ), 'Calendar'[Date] <> mindate )
RETURN
CALCULATE ( SUM ( 'ProjectX'[Gross Charges] ), prevprev6mos )
VAR mindate =
MIN ( 'Calendar'[Date] )
VAR prev12mos =
DATESINPERIOD ( 'Calendar'[Date], mindate, -12, MONTH )
VAR prev6mos =
DATESINPERIOD ( 'Calendar'[Date], mindate, -6, MONTH )
VAR prevprev6mos =
FILTER ( EXCEPT ( prev12mos, prev6mos ), 'Calendar'[Date] <> mindate )
RETURN
CALCULATE ( SUM ( 'ProjectX'[Gross Charges] ), prevprev6mos )
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat