Dynamic Relative Date

[Anonymous]

I am trying to create a measure to show month over month with the below exceptions.

Example on February 1st, instead of showing sales comparing February 1st to January 1st.  I want to show January 1st-31st of 2022 versus December 1st-31st of 2021.

Then on February 2nd I want it to switch back to showing February 1st-2nd versus January 1st-2nd of 2022.

Basically on the first day of the month I want it compare the full two months prior.

Then every other day of the month it is current month through yesterday versus prior month same date range

Any help is appreciated!

[Anonymous]

@amitchandak 

Thank you for your response.  The above worked for MTD pulling the first day of February, but for PMTD it is pulling the values for the first two days of January rather than just the first day of January to compare against the first day of February.  Is there an adjustment that can be made to only pull the first day of January?  The report is always looking back one day for reporting values.

[Anonymous · Accepted solution]

Hi @Anonymous,
Perhaps you can try to use the date function to directly define the date range to calculate:

PMTD =
VAR _end =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
VAR _start =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] >= _start
                && 'Table'[Date] <= _end
        )
    )

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , Try measures like

MTD=
var _max = if(day(today() )=1 , eomonth(today(),-1), today())
var _min = eomonth(_max,-1)+1

return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

LMTD =
var _max1 = if(day(today() )=1 , eomonth(today(),-1), today())
var _max = date(year(_max1),month(_max1)-1, day(_max1))
var _min = eomonth(today(),-2)+1

return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )