Forum Discussion
Using non-month end relative date
Hi shill1000,
You can try to use below formula to calculate specific date range who generated by current date.Measure version:
Dynamic result = VAR current_Date = MAX ( 'Table'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALL ( 'Table' ), [Date] >= DATE ( YEAR ( current_Date ), MONTH ( current_Date ) - 1, 27 ) && [Date] <= DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 26 ) ) )Calculate column version:
Dynamic result = CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] >= DATE ( YEAR ( EARLIER ( 'Table'[Date] ) ), MONTH ( EARLIER ( 'Table'[Date] ) ) - 1, 27 ) && 'Table'[Date] <= DATE ( YEAR ( EARLIER ( 'Table'[Date] ) ), MONTH ( EARLIER ( 'Table'[Date] ) ), 26 ) ) )Regards,
Xiaoxin Sheng
shill1000 ah ok you can always use the datesbetweenfunction i guess?
https://msdn.microsoft.com/en-us/library/ee634557.aspx
=CALCULATE(SUM(measure), DATESBETWEEN(table[date],
DATE(2007,6,1),
DATE(2007,8,31)
)) have to rush off to a meeting but there must be a way to make it dynamic
Thanks, but I decided to use a date table anyway. I added a column to calculate the financial month a date falls in (if date <27 then month else month+1). I then merged the 2 queries and pulled in the financial month value, then use month value as the filter. It's a bit clunky but it allows the reprot to be automated.
Thanks for the pointers.
Hi shill1000,
You can try to use below formula to calculate specific date range who generated by current date.Measure version:
Dynamic result = VAR current_Date = MAX ( 'Table'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALL ( 'Table' ), [Date] >= DATE ( YEAR ( current_Date ), MONTH ( current_Date ) - 1, 27 ) && [Date] <= DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 26 ) ) )Calculate column version:
Dynamic result = CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] >= DATE ( YEAR ( EARLIER ( 'Table'[Date] ) ), MONTH ( EARLIER ( 'Table'[Date] ) ) - 1, 27 ) && 'Table'[Date] <= DATE ( YEAR ( EARLIER ( 'Table'[Date] ) ), MONTH ( EARLIER ( 'Table'[Date] ) ), 26 ) ) )Regards,
Xiaoxin Sheng
Thanks Xiaoxin, I'll try that out and keep it in mind if I need a new solution or to repeat the current exercise. :)