Forum Discussion

vpatel55's avatar
vpatel55
Kudo Commander
4 years ago
Solved

Shifting dates one year back

I am trying to shift a column of dates by one year.  I have tried SAMEPERIODLASTYEAR, PARALLELLPERIOD and DATEADD. I want to avoid simply subtracting 365 days.   They all return the error "The True...
Need Help
  • vpatel55's avatar
    vpatel55
    4 years ago

    amitchandak pointed me in the right direction such that I could use the inbuilt time shifting functions but ignoring the current day. I'm including this post too if anyone finds it useful, as I wanted MTD last year, but ingoring the current day. Here is the code for that:

     

     

    MTD previous year = 
VAR _max =
    TODAY () - 1
VAR _min =
    EOMONTH ( _max, -1 ) + 1
RETURN
    CALCULATE (
        [Expression)],
        DATEADD ( DATESBETWEEN ( dimCalendar[Date], _min, _max ), -1, YEAR )
    )

     

     

amitchandak's avatar
amitchandak
Super User
4 years ago

vpatel55 , I doubt correct usage. Always use date tbale

refer examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

vpatel55's avatar
vpatel55
Kudo Commander
4 years ago

Hi amitchandak I'm afraid I can't easily use the inbuilt MTD functions because our data is one day behind. So on the 1st day of the month, it needs to refer to the whole of the previous month. Only on the 2nd of the month, should it start reporting for the current month. So I need to shift my StartDate and YesterdayDate dates. Any ideas?

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    vpatel55 , try this

     

    MTD a day before =
    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())-1
    var _min = eomonth(_max,-1)+1 ,
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

    or

     

    MTD a day before =
    var _max = if(isfiltered('Date'),MAXX(allselected('Date'), 'Date'[Date]) , today())-1
    var _min = eomonth(_max,-1)+1 ,
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

    You can also check

    https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27304

    • vpatel55's avatar
      vpatel55
      Kudo Commander
      4 years ago

      Hi amitchandak thanks for your response, I think we are nearly there, The top formula returned back some of today's numbers, which I wanted to avoid. However, removing the ISFILTERED and MAX date solves that, and brings the correct answer:

       

      Expression = 
var _max = today()-1
var _min = eomonth(_max,-1)+1
return
CALCULATE([Measure] ,DATESBETWEEN(dimCalendar[Date],_min,_max))

       Can you confirm that I have not ruined this so that it will still show the complete previous month on the 1st of the month, and it will still not begin counting this months numbers until the 2nd of the month (but always excluding the current day)?

       

      Thanks again.

      • vpatel55's avatar
        vpatel55
        Kudo Commander
        4 years ago

        amitchandak pointed me in the right direction such that I could use the inbuilt time shifting functions but ignoring the current day. I'm including this post too if anyone finds it useful, as I wanted MTD last year, but ingoring the current day. Here is the code for that:

         

         

        MTD previous year = 
VAR _max =
    TODAY () - 1
VAR _min =
    EOMONTH ( _max, -1 ) + 1
RETURN
    CALCULATE (
        [Expression)],
        DATEADD ( DATESBETWEEN ( dimCalendar[Date], _min, _max ), -1, YEAR )
    )