DATESYTD not working as expected

[Anonymous]

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 .

[Ashish_Mathur]

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.

[Anonymous · Accepted solution]

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

[parry2k]

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

[samdthompson]

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")

[samdthompson]

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

[Anonymous]

Tried this but doesnt work 

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