Forum Discussion

DonRSA's avatar
DonRSA
Frequent Visitor
2 years ago

Prior Year

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]))
Tutorial Requests

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

    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's avatar
      DonRSA
      Frequent Visitor
      2 years ago

      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

       

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

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

    • DonRSA's avatar
      DonRSA
      Frequent Visitor
      2 years ago

      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"
      )