Forum Discussion
DAX: YTD total
Hi
You need a date table in order to use the time intelligence functions in DAX.
A good start is looking at http://www.daxpatterns.com/time-patterns/
BR
Erik Svensen
You can do the customer level with the existing dax formula by wrapping it in calculate.
For example:
YTD Total Level 1 customers = CALCULATE([YTD Total], 'Customers'[CustomerLevel]=1)
This would assume that your customer level number is found in your customer table in a column called CustomerLevel.
Provide the true names and we can come up with a more exact calculation.
Thanks, i did try the solution you suggested, but i failed. Could you guide me how to get YTD total sales with Level 1 customers and Level 2 customers, and also how to use visualizations to show YTD sales Level 1, YTD sales Level 2, YTD total sales and Budget value?
| Month | Customer Contract | Cusomter level | Contract value |
| May | A | 1 | 50 |
| Jun | B | 2 | 150 |
| May | C | 1 | 159 |
| Jun | D | 1 | 351 |
| Jun | E | 2 | 21 |
| July | F | 2 | 55 |
| Aug | G | 1 | 69 |
| Aug | H | 2 | 128 |
hi michelle8816
1: You need a calendar table. You can create using this:
MyCalendar = CALENDAR("01/01/2015";"31/12/2016")
2. Create a column with dates in Sales Table.
Date = DATE(2015;SWITCH('Sales-MI88'[Month];"January";1;"February";2;"March";3;"April";4;"May";5;"June";6;"July";7;"August";8;"September";9;"October";10;"November";11;"December";12);01)
3. Create a relationship between Sales and your Calendar table. (Date & Date)
4. It's time to the measures for YTD
YTD = if(CALCULATE(sum('Sales-MI88'[Contract value]))>0;TOTALYTD(Sum('Sales-MI88'[Contract value]);'MyCalendar'[Date]);BLANK())
YTDL1 = if(CALCULATE(sum('Sales-MI88'[Contract value]))>0;TOTALYTD(Sum('Sales-MI88'[Contract value]);'MyCalendar'[Date];'Sales-MI88'[Customer level]=1);BLANK())
YTDL2 = if(CALCULATE(sum('Sales-MI88'[Contract value]))>0;TOTALYTD(Sum('Sales-MI88'[Contract value]);'MyCalendar'[Date];'Sales-MI88'[Customer level]=2);BLANK())
5. Create the visuals
