YTD Closed month (prior month) and when it is January it refers to December of prior year

[Anonymous]

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?

[amitchandak]

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

[nhoward]

Add Year and month slicers to the report page.  Then the formulas will know what month is current. 

[kentyler]

You could substitute using DATESBETWEEN() for DATESYTD, they both return a column of dates, but datesbetween would allow you to customize the date range.