Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic Relative Date

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 Januar...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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