Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

YTD with selected value

Hi, I have a YTD measure that shows YTD values until the end of last month, and it works fine if no Year is selected,

but if a previous  Year is selected in the slicer, the YTD value shows the whole year's value for the selected year, but I want to show 
For example, if the year 2020 is selected, YTD should show values until 2020-03-31

 

Thanks in advance!

 
YTD =
var ActualMonthBeginning = DATE(YEAR(TODAY()),MONTH(TODAY()),01)
var YearBeginning = DATE(YEAR(MIN('Date Table'[date])),01,01)
return
CALCULATE([sales total],
FILTER('Data'
,'Data'[Date] < ActualMonthBeginning
&& 'Data'[Date] >= YearBeginning
)
)

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Please try the below.

     

    YTD =
    VAR currentyear =
    MAX ( [yourcalendartable's year column] )
    VAR ActualMonthBeginning =
    DATE ( currentyear, MONTH ( TODAY () ), 01 )
    VAR YearBeginning =
    DATE ( YEAR ( MIN ( 'Date Table'[date] ) ), 01, 01 )
    RETURN
    CALCULATE (
    [sales total],
    FILTER (
    'Data',
    'Data'[Date] < ActualMonthBeginning
    && 'Data'[Date] >= YearBeginning
    )
    )

     

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Jihwan_Kim , what I was trying to achieve is to have YTD last year
    And the following formula works well.

     

    YTD Last Year=

    VAR currentyear =

    MAX ( 'Date Table'[year])

    VAR ActualMonthBeginning =

    DATE ( currentyear, MONTH ( TODAY () ), 01 ) var LY= EDATE(ActualMonthBeginning,-12)

    RETURN

    CALCULATE (

    [sales total],SAMEPERIODLASTYEAR('Date Table'[Date]),

    'Date Table'[Date] < LY

     

    )

     

     

6 Replies

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

    Anonymous , not very clear. Do you want to change the end of the year for YTD( means YTD start from a different date)

     

    example : year start/ytd start from 1st April

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

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Anonymous 

    Please try the below.

     

    YTD =
    VAR currentyear =
    MAX ( [yourcalendartable's year column] )
    VAR ActualMonthBeginning =
    DATE ( currentyear, MONTH ( TODAY () ), 01 )
    VAR YearBeginning =
    DATE ( YEAR ( MIN ( 'Date Table'[date] ) ), 01, 01 )
    RETURN
    CALCULATE (
    [sales total],
    FILTER (
    'Data',
    'Data'[Date] < ActualMonthBeginning
    && 'Data'[Date] >= YearBeginning
    )
    )

     

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Jihwan_Kim 

      Thanks; this works perfectly. How do I calculate the same period for last year?
      For example, if the year 2020 is selected, YTD LY should show the sales value for  (2019-01-01 to 2019-03-31)

      I am using the measure below but it gives values for the whole year when a year filter is selected 

       

      IsPast = 
      VAR LastTDate =

      DATE(YEAR(TODAY()),MONTH(TODAY()), 01)

      VAR LastSaleDatePY = EDATE ( LastTDate, -12 )
      RETURN
      'Date Table'[Date] < LastSaleDatePY
       
       
      YTD LY=
      CALCULATE([sales total],SAMEPERIODLASTYEAR('Date Table'[Date]),'Date Table'[IsPast] = True)
       
       

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        Hi, Anonymous 

        Thank you for your feedback.

        I am not quite sure whether I understood your last question correctly, but please check the below.

         

        VAR ActualMonthBeginning =
        DATE ( currentyear, MONTH ( TODAY () ), 01 )

         

        MONTH ( TODAY () ) inside the above VAR tells about how to limit the month number based on today's month. And combining with year, it can restrict the period selection.

         

        I hope I understood your question correctly.

        Thank you.