Using non-month end relative date

[shill1000]

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.

[vanessafvg]

@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

[shill1000]

Thanks 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 

[shill1000]

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.

[v-shex-msft · Accepted solution]

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]

Thanks Xiaoxin, I'll try that out and keep it in mind if I need a new solution or to repeat the current exercise. 🙂