Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to make YTD amount = 18 ?

Hi All

my YTD expression :-

 
YTD Price = TOTALYTD(Sum('order'[Price]),'Date'[Date])
 

My raw data each month from Jan till Sept total 9 month ,  Price = 2 ,  YTD should 18  ,  Since to day 7 Oct not yet arrive :-

now report YTD show 22 , how to make it display 18 , by modify the above expression :-

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , when there is not date slicer on the page. From where datesytd/totalytd know, where to stop.

    From the date you have passed in date parameter of these functions . Now they use that date and find out the last date and from there get ytd.

     

    So if your calendar ends 31-dec-2020 and you do not select a date on-page. it will show compelete year.

    If calendar goes till 2021, you might get blank for YTD if there is no slicer

     

    or try like

    YTD QTY forced=
    var _max = today()
    return
    if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())

4 Replies

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

    Anonymous ,  There is no date selected on the page. Then the last date in the calendar will be the end date of YTD.

     

    Have a slicer on the page or stop you calendar on today or yesterday

     

    calendar(date(2018,01,01), Today())

    calendar(date(2018,01,01), Today()-1)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Sir

       

      I am still not understand. why the initial start date will affect the YTD result ?  Also i though the YTD compute should be base on Order Table date ? 

       

      I though master calendar date range on starting and ending date should be dynamic ? why does it stop at 2020 12 31 ? does it mean that next year 2021 Jan i need to change the Date from 2020,12,31 to 2021,12,31 ?

       

      Date = CALENDAR(Date(2018,01,01),date(2020,12,31))
       
      Hope you can share with me pls.
       
      What should i change to make my YTD correct ?
       
       
      Above link is my PBI file.
       
      Paul Yeo
       
      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        Anonymous , when there is not date slicer on the page. From where datesytd/totalytd know, where to stop.

        From the date you have passed in date parameter of these functions . Now they use that date and find out the last date and from there get ytd.

         

        So if your calendar ends 31-dec-2020 and you do not select a date on-page. it will show compelete year.

        If calendar goes till 2021, you might get blank for YTD if there is no slicer

         

        or try like

        YTD QTY forced=
        var _max = today()
        return
        if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())