Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter value is equal to field value

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?

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

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

     

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

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