Prior Year

[DonRSA]

Hi Guys,

I have a problem with my Prior year measure when i use it to a calculated table,it's returning blanks on my figure column.

See below measure:

PriorYearFigure =

VAR SelectedYear = SELECTEDVALUE('KPI MTM COPY'[Fiscal Year])

VAR PriorYear = SelectedYear - 1

RETURN

CALCULATE(

SUM('KPI MTM COPY'[Figure]),

'KPI MTM COPY'[Fiscal Year] = PriorYear,'KPI MTM COPY'[Indicator] = "Actuals"

)

Now when i use it to this table below it returns  blanks.the measure is on the last selectcolumns.

KPI MTM Last = UNION(SELECTCOLUMNS('KPI MTM',"KPI",'KPI MTM'[KPI],"Brand",'KPI MTM'[Brand],"Month Name",'KPI MTM'[Month Name],"FYMonth",'KPI MTM'[FYMonth],"FYMNo",'KPI MTM'[FYMNo],"Fiscal Year",'KPI MTM'[Fiscal Year],"Figure",'KPI MTM'[Figure],"Indicator",'KPI MTM'[Indicator],"Pillar",'KPI MTM'[Pillar],"Day",'KPI MTM'[Day],"FY Date",'KPI MTM'[FY Date]),

SELECTCOLUMNS('KPI MTM COPY',"KPI",'KPI MTM COPY'[KPI],"Brand",'KPI MTM COPY'[Brand],"Month Name",'KPI MTM COPY'[Month Name],"FYMonth",'KPI MTM COPY'[FYMonth],"FYMNo",'KPI MTM COPY'[FYMNo],"Fiscal Year",'KPI MTM COPY'[Fiscal Year],"Figure",'KPI MTM COPY'[Variance MTM],"Indicator","Variance Budget","Pillar",'KPI MTM COPY'[Pillar],"Day",'KPI MTM COPY'[Day],"FY Date",'KPI MTM COPY'[FY Date]),

SELECTCOLUMNS('KPI MTM COPY',"KPI",'KPI MTM COPY'[KPI],"Brand",'KPI MTM COPY'[Brand],"Month Name",'KPI MTM COPY'[Month Name],"FYMonth",'KPI MTM COPY'[FYMonth],"FYMNo",'KPI MTM COPY'[FYMNo],"Fiscal Year",'KPI MTM COPY'[Fiscal Year],"Figure",'KPI MTM COPY'[Variance Forecast],"Indicator","Variance Forecast","Pillar",'KPI MTM COPY'[Pillar],"Day",'KPI MTM COPY'[Day],"FY Date",'KPI MTM COPY'[FY Date]

),SELECTCOLUMNS('KPI MTM COPY',"KPI",'KPI MTM COPY'[KPI],"Brand",'KPI MTM COPY'[Brand],"Month Name",'KPI MTM COPY'[Month Name],"FYMonth",'KPI MTM COPY'[FYMonth],"FYMNo",'KPI MTM COPY'[FYMNo],"Fiscal Year",'KPI MTM COPY'[Fiscal Year],"Figure",'KPI MTM COPY'[PriorYearFigure],"Indicator","Prior Year","Pillar",'KPI MTM COPY'[Pillar],"Day",'KPI MTM COPY'[Day],"FY Date",'KPI MTM COPY'[FY Date]))

[HotChilli]

You can create a calculated column or a custom column in Power Query

[DonRSA]

Hi i've never done that, see my measure , how will i do it?

Prior Year FigureMeas =

CALCULATE(

SUM('KPI MTM Last'[Figure]),

FILTER(

ALL('KPI MTM Last'[Fiscal Year]),

'KPI MTM Last'[Fiscal Year] = SELECTEDVALUE('KPI MTM Last'[Fiscal Year]) - 1

),

'KPI MTM'[Indicator] = "Actuals"

)

[HotChilli]

It is not a good idea to use measures in table or column definitions.  Use measures in visuals.

--

It will be getting blank values because of the evaluation context but not a good idea to go this route

[DonRSA]

Thanks for the reply,but i wanted this measure in the table, i guess will just have to use it as a measure.looks terrible when it'on the matrix