Forum Discussion

Kt1097's avatar
Kt1097
New Member
5 years ago
Solved

Dynamic YTD across multiple years

Hello,   after hours of reading different posts and trying out formulas, I still haven't found a solution for my problem. There are two columns from my table that are relevant here: Sales units an...
Need Help
  • Kt1097's avatar
    Kt1097
    5 years ago

    We finally came up with a measure that works for our purpose:

    YTD sales units =

    TOTALYTD(SUM('Sales'[Sales units]); 'Sales'[Date];MONTH('Sales'[Date])<=MONTH(NOW())

amitchandak's avatar
amitchandak
Super User
5 years ago

Kt1097 , Try a YTD like this

example

YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • Kt1097's avatar
    Kt1097
    New Member
    5 years ago

    Thanks for your answer. Still, is there a possibility to do it without the date calendar table? I already have a lot of different tables in my file...