over ride month filter to get YTD Sales

[sadath]

Hi

Something wrong in formula to yet YTD sales

:=calculate(sum(SALESDATA[AMOUNT]),SALESDATA[year]=2015,all([MonthCode]),filter(SALESDATA,SALESDATA[MonthCode]<=[Mon2]))


[MonthCode] column having month numbers like 1,2,3,...12
[Mon2] is max([MonthCode])


i want to override my month filter to get YTD sales. ie if i select 11 in filter should give sales from month 1 to 11

 

[Matt Allington]

You should consider using a calendar table rather has having so many calendar columns in your data table. Power Pivot Calendar Tables - Excelerator BI

you should always specify tablename[column name] in your formulas (never refer to [column name]). This avoids confusion with [measures]

try this

[COLOR=#333333]:=calculate(sum(SALESDATA[AMOUNT]),filter(all(SALESDATA[monthcode],salesdata[year]),SALESDATA[MonthCode]<=[Mon2]&& salesdata[year]=max(salesdata[year])))[/COLOR]

i dont normally rite such measures without a calendar table, but I think it will work.

 

[sadath]

Hi
Thank you for your valuable suggestions, I have already made my data for 2016, so cant chage

unfortunately the DAX is not working

:=calculate(sum(SALESDATA[AMOUNT]),filter(all(SALESDATA[monthcode],salesdata[year]),SALESDATA[MonthCode]<=[Mon2]&& salesdata[year]=2015))

[MonthCode] is related to another table as below

MonthCode Month
1 Jan
2 Feb
3 Mar etc...


Month slicer showing month name (Jan,Feb,Mar..)from the month table.

 

[Matt Allington]

My formula was based on my understanding of what you had, but you have different tables. What you have is not a good design and I recommend you step back and get the table structure right first. If you don't want to do that, please post a sample workbook here with what you have already done and I will try to give you a formula that works.