Calculate with filters problem

[BKNewton29]

All,

I'm currently trying to create three measures that will sum up a year to date figure for some data for three different years. The filter criteria is linked to a different table that updates from a connected table in Excel. The formula for the current year works fine (see below)

ActualValueYTD:=CALCULATE(SUM(SAGE[ActualValue]),FILTER(SAGE,SAGE[Year]=RELATED(Periods[Year])),filter(SAGE,SAGE[Month]<=RELATED(Periods[Month])))

However as soon as I change this to look at the prior year it returns a zero. I assume this is because I've filtered down to 2016 in the formula above so it can't see 2015 in the next formula (see below)


PYValueYTD:=CALCULATE(SUM(SAGE[ActualValue]),FILTER(SAGE,SAGE[Year]=RELATED(Periods[Prior])),filter(SAGE,SAGE[Month]<=RELATED(Periods[Month])))


As I'm new to DAX any help would be appreciated

thanks

 

[akice]

Is the 'Periods' table a true calendar table with a 'date' column (like 03/18/2016)? how is it related to 'SAGE' in data model? I ask because what you want to do should be simple enough, but need to know more information. Time Intelligence functions like TOTALYTD may make the most sense, but again depends on how the model is setup. You also may need to do something like this for prior year:

PYValueYTD:=CALCULATE(SUM(SAGE[ActualValue]),FILTER(SAGE,SAGE[Year]=RELATED(Periods[Year]) - 1 ),filter(SAGE,SAGE[Month]<=RELATED(Periods[Month])))

 

[BKNewton29]

It was a linked table to a pivot table in Excel.

I've managed to find a solution this evening by creating a variable that links to that table and is then used in the table.