Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DATESYTD not working as expected

I'm trying to get YEAR to DATE sum based on a customer financial year.

 

Here are my code snippets:

AMT_SUM = 
CALCULATE(
    SUM(sql[AMT])
    )*-1

 

YTD = 
CALCULATE(
    [AMT_SUM],
    DATESYTD('Date'[Date],"31/03")
)

But as you can see, the YTD function starts on April as intended then resets on January 2021? Why is that ?

My fact table Date column is joined with my Date table on Date key .

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI Anonymous,

    Did these formulas help? If not, you can also try to use the date function to manually define the filter ranges to calculate the YTD which is similar to the DATESYTD function.

    YTD =
CALCULATE (
    [AMT_SUM],
    FILTER (
        ALLSELECTED ( sql ),
        sql[Date] <= MAX ( Calendar[Date] )
            && YEAR ( sql[Date] ) = YEAR ( MAX ( Calendar[Date] ) )
    )
)

    Time Intelligence "The Hard Way" (TITHW)  

    Regards,

    Xiaoxin Sheng

6 Replies

  • samdthompson's avatar
    samdthompson
    Icon for Memorable Member rankMemorable Member
    5 years ago

    Hello, DATESYSTD will return a column with the dates up to now. What you want I think is TOTALYTD.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Tried this but doesnt work 

      YTD = 
TOTALYTD(
    [AMT_SUM],
    'Date'[Date],
    ALL('Date'),
    "3-31"
)

       

       

       

  • samdthompson's avatar
    samdthompson
    Icon for Memorable Member rankMemorable Member
    5 years ago

    Hello, just checked the documentation from MS. Looks like the Year end date is best done as a "3/31" rather than "3-31":

     

    In this example, year_end_date can be specified as "6/30", "Jun 30", "30 June", or any string that resolves to a month/day. However, it is recommended you specify year_end_date using "month/day" (as shown) to ensure the string resolves to a date.

     

    Try:

     

    YTD = TOTALYTD([AMT_SUM], Date[Date], "03/31")

     

     

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

    Anonymous here is what you need to change:

     

    YTD = 
CALCULATE(
    [AMT_SUM],
    DATESYTD('Date'[Date],"3/31")
)

     

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Anonymous,

    Did these formulas help? If not, you can also try to use the date function to manually define the filter ranges to calculate the YTD which is similar to the DATESYTD function.

    YTD =
CALCULATE (
    [AMT_SUM],
    FILTER (
        ALLSELECTED ( sql ),
        sql[Date] <= MAX ( Calendar[Date] )
            && YEAR ( sql[Date] ) = YEAR ( MAX ( Calendar[Date] ) )
    )
)

    Time Intelligence "The Hard Way" (TITHW)  

    Regards,

    Xiaoxin Sheng

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

    Hi,

    Your measures are correct.  Just that you can simplify Amt_Sum to

    =-1*(SUM(sql[AMT]))

    I suspect there is a problem with the relationship.  Share the link from where i can download your PBI file.