The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I've been using the datesytd() and sameperiodlastyear() to get YTD to analyze the YTD spend. I refresh the data after the period closes. Normally if I do this quick enough there are no postings for the current month so it isn't a problem.
However - if I'm ever late then there are postings in the current month that I don't want because the period isn't closed AND now that is is January and I want to show YTD December, I'm getting 0's because there is no YTD January 2020 data.
How would you fix this? Its not as simple as subtracting a month. Maybe some sort of days(today()) function to subtract from the date?
Try to restrict it by max available date
YTD Sales =
var _max = maxx(Sales,Sales[Date])
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date]),'Date'[Date]<_max)
//OR
//CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date]),Sales[Date]<_max)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Add Year and month slicers to the report page. Then the formulas will know what month is current.
You could substitute using DATESBETWEEN() for DATESYTD, they both return a column of dates, but datesbetween would allow you to customize the date range.
Help when you know. Ask when you don't!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
33 | |
20 | |
16 | |
15 |
User | Count |
---|---|
56 | |
50 | |
36 | |
35 | |
31 |