Forum Discussion

yaman123's avatar
yaman123
Post Partisan
5 years ago

YTD Volume

Hi,    I am having an issue calculating the YTD volume from a dataset.    I have a supply end date date slicer which is a between e.g user selects 01/02/2021 - 28/02/2021   I need to calculate ...
Need Help
Show and Tell
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
5 years ago

yaman123 , YTD will be from the start of the year

 

This seems like sum(Table[collection_liter])

 

YTD with help from date table

calculation(sum(Table[collection_liter]) ,datesytd('Date'[Date]) )

 

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.

 

yaman123's avatar
yaman123
Post Partisan
5 years ago

Hi, 

 

I need the YTD to be -11 months from the chosen dates. How can this be done? 

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    yaman123 , Try like

     

    Rolling 11 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-11,MONTH))

     

    or

     

    measure =
    var _max = maxx(allselected('Date1'), 'Date1'[Date])
    var _min = eomonth(minx(all('Date'), 'Date'[Date]),-11)
    return
    calculate(sum(Sales[Sales Amount]), filter(all('Date'),'Date'[Date] <= _max && 'Date'[Date] >=_min))

    • yaman123's avatar
      yaman123
      Post Partisan
      5 years ago

      Hi, 

       

      That also doesnt work. My slicer is is a between selection, so two dates are selected. I am not getting the correct figures from the code you have provided.