Filter measures

[jorge_oliveira]

Hello,

I'm relatively new to Power BI. I'm stuck with a formula for some time now so any help would be appreciated.

Is it possible to filter a measure with a number selected in a slicer and then stored in another measure?

I have two slicers: year and month, both are numbers. E.g. 2021 for year and 3 (March) for month.

I need two measures:

1 - Sum only for that year and month (3).

2- Cumulative total for that year up to month number 3.

My fact table looks something like this:

Products  Year    Month  Value

A              2021   1           10

A              2021   2           10

A              2021   3           30

A              2021   4           10

B              2021   1           20

B              2021   2           10

B              2021   3           40

B              2021   4           10

C              2021   1           15

C              2021   2           10

C              2021   3           20

C              2021   4           10

The results I expect are these:

Products   Measure 1   Measure 2

A              30                 50

B              40                 70

C              20                 45

Thanks.

Jorge

[jorge_oliveira]

Hello,

I'm still working on this formula and need extra help.

I have the following model:

I need the Cumulative total for the selected year/month and I'm using the formula:

AMT_BALANCE_YTD =

VAR YEAR = SELECTEDVALUE(PROFIT_CENTER[YEAR])

VAR MONTH = SELECTEDVALUE(PROFIT_CENTER[MONTH])

return

CALCULATE([Accumulated Balance];

FILTER(ALLEXCEPT(PROFIT_CENTER;DIM_STRUCTURE);PROFIT_CENTER[YEAR] = YEAR && PROFIT_CENTER[MONTH] <= MONTH))

The measure [Accumulated Balance] is: [Accumulated Balance] = SUM(PROFIT_CENTER[AMT_BALANCE])+0

What isn't working is when I select a month that doesn't have any value to a specific ID_COST_CENTER / ID_ACCOUNT, as in the example below (in green what I expect to see in a card depending on the year/month selected and in red what I'm getting with the formula I'm using.

How can I change the formula to obtain the desired results?

THanks for your help.

Jorge

[jorge_oliveira]

Hi @VahidDM ,

I apologize for the insistence or if I'm being intrusive, but is there any change I can make to the measure to get the desired results as I described in the previous message?

For example, in May the result should be 4306 and not zero.

Thanks.

Jorge

[VahidDM · Accepted solution]

Hi @jorge_oliveira 

Try these 2 measures:

Measure 1 = 
Var _Y = SELECTEDVALUE('Table'[Year])
Var _M = SELECTEDVALUE('Table'[Month])
return
CALCULATE(SUM('Table'[Value]),filter(ALLEXCEPT('Table','Table'[Products]),'Table'[Year]=_Y&&'Table'[Month]=_M))

Measure 2 = 
Var _Y = SELECTEDVALUE('Table'[Year])
Var _M = SELECTEDVALUE('Table'[Month])
return
CALCULATE(SUM('Table'[Value]),filter(ALLEXCEPT('Table','Table'[Products]),'Table'[Year]=_Y&&'Table'[Month]<=_M))

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[jorge_oliveira]

Hi @VahidDM,

Thanks. It almost works. And that's because I've simplified one thing that I thought it wouldn't affect.

I have a dimension table to Products and to the resulting table I'm using the field from DIM_PRODUCTS. I need this, because there's a third table with the expected quantity for products and it could exist Porduct D on that table and not on the first table.

So the exact model is:

[TABLE A]

Products  Year    Month  Value

A              2021   1           10

A              2021   2           10

A              2021   3           30

A              2021   4           10

B              2021   1           20

B              2021   2           10

B              2021   3           40

B              2021   4           10

C              2021   1           15

C              2021   2           10

C              2021   3           20

C              2021   4           10

[TABLE B]

Products  Year    Month  Value

A              2021   1           10

A              2021   2           10

A              2021   3           30

A              2021   4           10

B              2021   1           20

B              2021   2           10

B              2021   3           40

B              2021   4           10

C              2021   1           15

C              2021   2           10

C              2021   3           20

C              2021   4           10

D              2021   1           15

D              2021   2           10

D              2021   3           20

D              2021   4           10

[DIM_PRODUCTS]

Products Value

A             5

B             5

C             5

D             5

Table A and Table B are both related to DIM_PRODUCTS.

[jorge_oliveira]

In the results table I will also have two measures using Table B, identical to the other two measures, so I would expect something like this:

The results I expect are these:

Products   Measure 1   Measure 2   Measure 3   Measure 4

A              30                 50               30                 50

B              40                 70               40                 70

C              20                 45               20                 45

D              0                   0                 20                 45