I used the following formula to find YTD and YTD LY
YTD = CALCULATE(SUM('data'[konto]),
FILTER('year',[Date]>=DATE(YEAR(TODAY()),1,1) && [Date]
YTD LY = CALCULATE(SUM('data'[konto]),
FILTER('year',[Date]>=DATE(YEAR(TODAY())-1,1,1) && [Date]
when I select year 2018 It only showes me YTD, how can I fix this to look both YTD and YTD LY when year 2018 is selected?
Is it possible to get the logic below with the exception that it calcultates sales amount with a start date based on selected value from a date slicer?
Sales Amount YTD based on current date = VAR Today = TODAY () VAR TodayMonth = MONTH ( Today ) VAR TodayDay = DAY ( Today ) VAR MaxDateSelectedYear = YEAR ( MAX ( 'Date'[Date] ) ) VAR TodayShiftedToSelectedYear = DATE ( MaxDateSelectedYear, TodayMonth, TodayDay ) RETURN CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date] ), CALCULATETABLE ( DATESYTD ( 'Date'[Date] ), 'Date'[Date] = TodayShiftedToSelectedYear ) )
At the moment it always return YTD from 01-Jan to max TODAY or to a selected date less than TODAY. But when selecting the date range with start 10-Jan-19 to 17-Jan-19 it still returns the sales amount for 01-Jan-19 to 17-Jan-19.
That mean you are not after really YTD rather data between selected dates. If you select full year, you want actually YTD data and if you select some mid range of dates, you want data for those dates only, hence aggrigation for selected dates and not YTD.
In that case, don't use DATESYTD because this will always start from first day of selected year. May be something like below:
Sales Amount YTD based on current date = CALCULATE ( [Sales Amount], FILTER ( 'Date', 'Date'[Date] >= MIN ( 'Date'[Date] ) ), FILTER ( 'Date', 'Date'[Date] <= 'Date'[Date] ) )
Thank you very much @emudria!
That worked exactly as I wanted it to. How would you create a LY-measure to behave accordingly for the same period last year? Assume it's a slight change in the definition of date filtering.
I tried this formula but it doesn't set a last date to maximum TODAY -1 year.
It works fine as long as I don't extend my date filter to more than TODAY.
If I for example set the date filter to 1-jan-19 to 31-dec-19 the Sales Amount LY measure will sum upp sales for 1-jan-18 to 31-dec-18. The behaviour I'm looking for is that it in this case only calculate for 1-jan-18 to 13-feb-19 (today - 1 year as max).
Thought that if I use a YTD measure the way you fomulated it, I would do exactly that.. 🙂
So my problem is really that I would like a LY-measure that use MIN selected day -1 year and then as TODAY -1 year as MAX selected value.
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!