ITD Metric in Power BI

[akj2784]

Hi All,

I have data like below.

Type Year Revenue

A      2016    100

A      2017    200

I want to create a report for year = 2017 as below 

Type  Revenue YTD      Revenue ITD

A         200                    300

How to create Revenue ITD Metric so that even if I apply filter on year=2017, it should still consider 2016 records and show it in ITD metric. Running SUM will not help here.

Regards,

Akash

[Stachu · Accepted solution]

I assume the ITD is about all years before the one that is selected

Revenue ITD:=
VAR SelectedYear = MAX(Table1[Year])
RETURN
CALCULATE(SUM(Table1[Revenue]),Table1[Year]<=SelectedYear)

[Thejeswar · Accepted solution]

HI @akj2784,

Use the below DAX statement

Revenue ITD = CALCULATE(SUM(Table3[Revenue YTD]), FILTER(ALL(Table3[Year]), Table3[Year] <= SELECTEDVALUE(Table3[Year])))

Screenshot below for your reference, The Filter applied is Year = 2018

Regards,

Thejeswar

[Stachu · Accepted solution]

I assume the ITD is about all years before the one that is selected

Revenue ITD:=
VAR SelectedYear = MAX(Table1[Year])
RETURN
CALCULATE(SUM(Table1[Revenue]),Table1[Year]<=SelectedYear)