Forum Discussion
Solved
Using non-month end relative date
Hope someone has a solution for this. i have a need to report data every month on a financial period, not calendar month. This will be 27th month A to 26th month B. I can obviously hard code the ...
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
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. :)