Forum Discussion

rioshox's avatar
rioshox
New Member
4 years ago
Solved

YTD For All Years

Hello,

 

I got a calendar and a sales table (starting from 2018) and i am looking to get YTD of all years up to the latest date based on selected year month.

 

Let's say the user selects 2022-Feb then it should return YTD Sales for 2018-Feb, 2019-Feb, 2020-Feb, 2021-Feb  and 2022-Feb. Same thing as when 2022-Jan is selected YTD Sales for 2018-Jan, 2019-Jan, 2020-Jan, 2021-Jan and 2022-Jan.

 

If the user selects 2020-Mar then it should return 2018-Mar, 2019-Mar  2020-Mar. 

Any help is highly appreciated.

 

  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi rioshox 

    The key to solving the problem is to get the respective period of each year according to the month selected by the slicer. Firstly, you need to have an independent date table, use it as slicer.

    (1) create the Date table

    (2) create the measure bellow, 

    Measure = 
    var _selectedMonth = MONTH(SELECTEDVALUE('Date'[Date]))
    var _currentMonth = DATE(YEAR(MIN('Table'[Date])),_selectedMonth,1)
    var _dateStart = EDATE(_currentMonth,-12)
return CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[Date] >= _dateStart && 'Table'[Date] <= _currentMonth))

    as you can see above, if you select 2022-Jan, then _currentMonth = 2022 - Jan, _dateStart = 2021 - Jan, then Measure returns total of this period.

     

     

    But if you want to get same period in last years, try this 

    Measure2 = 
    var _selectedMonth = MONTH(SELECTEDVALUE('Date'[Date]))
return CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),MONTH('Table'[Date])= _selectedMonth && YEAR('Table'[Date])= year(MIN('Table'[Date]))))

    Besides, if you want to add total to this visual, try this

    Measure2-1 = 
    var _selectedMonth = MONTH(SELECTEDVALUE('Date'[Date]))
return CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),MONTH('Table'[Date])= _selectedMonth && YEAR('Table'[Date])= year(MIN('Table'[Date]))))
    Measure2-2 = 
    var _selectedMonth = MONTH(SELECTEDVALUE('Date'[Date]))
return SUMX(FILTER(ALL('Table'),MONTH('Table'[Date])= _selectedMonth),[Measure2-1])
    Measure2 = IF(ISINSCOPE('Table'[Date].[Year]),[Measure2-1],[Measure2-2])

     

     

    Best Regards,

    Community Support Team _Tang

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

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi,

    For a basic YTD calculation you can use pattern like this: CALCULATE([Your measure],DATESYTD('Calendar'[Date])). Then to get your desired end result you can add to this LY, LY-1, LY-2 and so on. Do you have a set amount of years you want to calculate together (e.g. 4) or all the years for all time? If this is the case we might need a different dax.

    This DAX resets every year and adds all the values prior to the max month:

    YTD =
    var maxdate = MAX('Calendar example'[Date])
    var _month = month(MAX('Calendar'[Date]) )
    var _year = YEAR(MAX('Calendar'[Date]) )
    return
    CALCULATE(SUM(Cumulativetotal[Value]),ALL('Calendar'),'Calendar'[Date]<=maxdate,'Calendar'[Month]<=_month,'Calendar'[Year]<=_year)
     


    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     If you want to use year slicer this might be for you other wise you need to calculte ytd for all years
    first you need to calculate year start date 

    year start date = STARTOFYEAR(DateTime[DateKey])

    then you need to create year rank using this logic:

    year rank = RANKX(all('calender table'),'calender table'[year start date],,ASC,Dense)

    then use this logic to calculate ytd:

    Ytd = 
CALCULATE(SUM('sales table'[sales]),FILTER(all(calender table),calender table[year rank]=max(calender table[year rank])-1))



  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi rioshox 

    The key to solving the problem is to get the respective period of each year according to the month selected by the slicer. Firstly, you need to have an independent date table, use it as slicer.

    (1) create the Date table

    (2) create the measure bellow, 

    Measure = 
    var _selectedMonth = MONTH(SELECTEDVALUE('Date'[Date]))
    var _currentMonth = DATE(YEAR(MIN('Table'[Date])),_selectedMonth,1)
    var _dateStart = EDATE(_currentMonth,-12)
return CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[Date] >= _dateStart && 'Table'[Date] <= _currentMonth))

    as you can see above, if you select 2022-Jan, then _currentMonth = 2022 - Jan, _dateStart = 2021 - Jan, then Measure returns total of this period.

     

     

    But if you want to get same period in last years, try this 

    Measure2 = 
    var _selectedMonth = MONTH(SELECTEDVALUE('Date'[Date]))
return CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),MONTH('Table'[Date])= _selectedMonth && YEAR('Table'[Date])= year(MIN('Table'[Date]))))

    Besides, if you want to add total to this visual, try this

    Measure2-1 = 
    var _selectedMonth = MONTH(SELECTEDVALUE('Date'[Date]))
return CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),MONTH('Table'[Date])= _selectedMonth && YEAR('Table'[Date])= year(MIN('Table'[Date]))))
    Measure2-2 = 
    var _selectedMonth = MONTH(SELECTEDVALUE('Date'[Date]))
return SUMX(FILTER(ALL('Table'),MONTH('Table'[Date])= _selectedMonth),[Measure2-1])
    Measure2 = IF(ISINSCOPE('Table'[Date].[Year]),[Measure2-1],[Measure2-2])

     

     

    Best Regards,

    Community Support Team _Tang

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