BKNewton29
New Member
- Joined
- Sep 17, 2010
- Messages
- 30
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
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