Filter value is equal to field value

[michielkuiper]

For example i have 4 tables;

Table1: CurrentPeriod:

In this table there is only one record and this record is the current period of periodrange: 

ID: 30050

Year: 2019

Week: 50

Table2: Periods

In this table all periods are stored:

ID: 30001

Year: 2019

Week: 1

ID: 30002

Year: 2019

Week 30003

en so on... 

Table3: Revenue

In this table all the revenue by week is stored:

PeriodID: 30001

Revenue: 8000,-

PeriodeID: 30002

Revenue: 8500.- 

and so on...

Table3: RevenueGoal

In this table all the revenue goals by week are stored:

PeriodID: 30001

Revenue: 7800,-

PeriodeID: 30002

Revenue: 8300.- 

and so on...

I would to show an graph with

• Y axis by Revenue
• X axis by Period (week)
• Values2: Sum of Revenue
• Values2: Sum of RevenueGoal

So far so good, but i will show all weaks of the year. We have a graph with the RevenueGoal for whole 2019 but we have none realisatin for whole 2019. It is posible by selecting all the weeks of the year. But I also will show the Revenue of the Current Period.

So I want to add a filter that wil sum de Avanue where PeriodeID of the AvenueTable is equal tot the periodID of the CurrentPeriod Table. Is that possible?

[v-frfei-msft · Accepted solution]

Hi @michielkuiper ,

I have created two measures as below to work on it. Please check the pbix as attached.

YTD = 
CALCULATE (
    SUM ( Revenue[Revenu] ),
    FILTER (
        ALL ( Period ),
        Period[Year] = MAX ( CurrentPeriod[Year] )
            && Period[Week] <= MAX ( Period[Week] )
            && Period[Week] <= MAX ( CurrentPeriod[Week] )
    )
)

this week = 
CALCULATE (
    SUM ( Revenue[Revenu] ),
    FILTER (
        Period,
        Period[Week] = MAX ( CurrentPeriod[Week] )
            && Period[Year] = MAX ( CurrentPeriod[Year] )
    )
)

[amitchandak]

 If possible please share a sample pbix file after removing sensitive information.
Thanks

[michielkuiper]

Of course it is. 

See  Example file