topic Re: YTD For All Years in DAX Commands and Tips

YTD For All Years

rioshox — Thu, 03 Feb 2022 06:47:42 GMT

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.

Re: YTD For All Years

ValtteriN — Thu, 03 Feb 2022 07:21:23 GMT

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/

Re: YTD For All Years

Anonymous — Thu, 03 Feb 2022 12:14:47 GMT

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

Re: YTD For All Years

v-xiaotang — Fri, 11 Mar 2022 06:51:52 GMT

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.