Forum Discussion

TheHans's avatar
TheHans
Icon for Helper I rankHelper I
4 years ago
Solved

Create a measure with aggregated values considering existing filters

Dear Community,

 

I have a project table containing many projects and values per month. I would like to create a measure that accumulates all values p to a certain date but considering existing filters, such as for example only for "Project A" if this is chosen in the visual filters.

 

ProjectFinancials Table

ProjectIDValuePeriod YMPropertyABC
Project A10202201e
Project A20202202f
Project A30202203g
Project A25202204g
Project A48202205f
Project A15202206e
Project B84202201r
Project B85202202r
Project B29202203r
Project B67202204z
Project B28202205z
Project B44202206s

 

Now I have created a measure like this, however this one does not consider any filters (since I chose "ALL").

 

PlannedAcc = 

VAR EndDate = SELECTEDVALUE(DateTable[Period YM]) // this is a value YYYYMM

RETURN

 SUMX(

 CALCULATETABLE(

   'FACT-Project Financials', 

   ALL('FACT-Project Financials'),

   'FACT-Project Financials'[Period YM] <= EndDate
 ),

 'FACT-Project Financials'[Value]

 )

I plan to use a clustered column chart with the months on the x-axis and the cumulated values per month. Any filter applied to the chart, such as project ID or some other property needs to take effect on the cumulated values.

 

Can anyone help?

 

Thanks in advance

H.

Need Help
  • mwegener's avatar
    mwegener
    4 years ago

    Hi TheHans ,

     

    try this.

    PlannedAcc = 

VAR EndDate = MAX(DateTable[Period YM]) // this is a value YYYYMM

RETURN

CALCULATE(
    SUM('FACT-Project Financials'[Value]),
    FILTER(ALL(DateTable), DateTable[Period YM] <= EndDate)
)

2 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi TheHans ,

     

    try this.

    PlannedAcc = 

VAR EndDate = MAX(DateTable[Period YM]) // this is a value YYYYMM

RETURN

CALCULATE(
    SUM('FACT-Project Financials'[Value]),
    FILTER(ALL(DateTable), DateTable[Period YM] <= EndDate)
)