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 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!

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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 ) )

  • 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