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

     

    )

     

     

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.

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

      Hi, Anonymous 

      Perhaps, I am missing something....

      If it is OK with you, please share your sample pbix file's link here, then I can try to understand more clearly.

      Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        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

         

        )