Forum Discussion

shill1000's avatar
shill1000
Helper IV
8 years ago
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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
shill1000
Helper IV
8 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    shill1000
    Helper IV
    8 years ago

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