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 the YTD Volume from the chosen date period. 

 

Below is an example of the YTD data if 01/02/2021 - 28/02/2021 is selected. 

 

 

TIA

Need Help
Show and Tell
Tips and Tricks

5 Replies

  • 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))

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi yaman123

     

    Create Date_slicer table as:

    Date Slicer = 
CALENDAR(MIN('Table'[SUPPLY_END_DATE]),MAX('Table'[SUPPLY_END_DATE]))

    Try measure as:

    Measure = 
var min_date=
EOMONTH(MINX('Date Slicer',SELECTEDVALUE('Date Slicer'[Date],MAX('Date Slicer'[Date]))),-11)
var max_date=
MINX('Date Slicer',SELECTEDVALUE('Date Slicer'[Date],MAX('Date Slicer'[Date])))
return 
CALCULATE(
    MAX('Table'[COLLECTION_LITRES]),
    FILTER(
        ('Table'),
        'Table'[SUPPLY_END_DATE]<=max_date && 'Table'[SUPPLY_END_DATE]>=min_date 
    )
)

    Here is the output:

    The pbix file is attached.

     

    Best Regards,
    Link

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.