Forum Discussion
Helper IVUsing 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 dates into the query, or I can use a secondary table with the dates to be passed as parameters, but ideally I want a query that always runs for the 27th day of Previous Month to 26th day of Current Month.
I was figuring that i should be able to try something like:
Date 1 = start previous month +26
Date 2 = start current month +27 (then use less than Date 2 as end of period)
I get the logic, I just can't work out the syntax.
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
6 Replies
- vanessafvg
Community Champion
shill1000 i am assuming you have a date table?
Another way of doing it is creating
1. FiscalDayofmonth
2 FiscalMonth
3 Fiscal Year columns?
and then you can use those fields to filter you info by
shill1000Thanks for the swift response.
I haven't needed a date table yet as it's a simple report with no other date handling to warrant a date table. All I want the query to do is fetch the relative months records with minimum fuss. As it will only ever fetch one months records, and always between these days, I can't help thiniking that inserting fiscal year/month/day columns will be overkill. If it is the only solution then I guess I must do that, but I can't help thinking there has to be a simpler more elegant solution.
- vanessafvg
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
