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 .
1 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
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.
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
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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")
Hello, DATESYSTD will return a column with the dates up to now. What you want I think is TOTALYTD.
Tried this but doesnt work
YTD =
TOTALYTD(
[AMT_SUM],
'Date'[Date],
ALL('Date'),
"3-31"
)
