DAX: YTD total

[michelle8816]

I have the data including customer, contract month, and contract value, and then i use DAX to creat sales and YTD sales by following measures.

Sales = sum(Sales[Contract Value])

YTD Sales = totalytd([Sales];Sales[Contract month])

however the calculation of YTD sales should be cumulated, but it did not like that.  I want to show the cumulative curve on Visualizations, how can i do that?

[donsvensen · Accepted solution]

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

[michelle8816]

 but if there is a condition, only want to calculate YTD Sales or cumulative sales when the customer is belong to LEVEL 1, as the company has set up different leverls for different customers.

How  DAX expression shall be be?

[kcantor]

@michelle8816

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.

[michelle8816]

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

[Vvelarde]

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

[donsvensen]

You should properly look at the CALCULATE and combine that with the necessary filter expression to solve that.

If you can supply a data example, I will try to give the expression needed a try 🙂