Conditional measure that reacts to filters

[Anonymous]

Hello everyone, 

I am new to DAX, and I would like to create a measure that is a little bit tricky 

I have many versions of amounts, and these amounts are flagged and I will simplify the concept with an example 

Amount	Version
120000$	v1
120000$	v2
120000$	v3

Month : 1 --> 3 = v1

4 --> 8 = v2

9 --> 12 = v3

Here is my try that works in a table not with other graphics : 

Amounts v = 

VAR _current_month = MONTH(Today())
VAR _current_year = MAX(Budget[Year])
VAR _last_year = _current_year - 1
VAR _BN = "B" & _current_year 
VAR _BN_v03 = "B"&_current_year & " v03"
VAR _BN_v05 = "B"&_current_year & " v05"
VAR _BN_v07 = "B"&_current_year & " v07"
VAR _BN_v10 = "B"&_current_year & " v10"

VAR _BN_amounts = CALCULATE([Total amounts], 
    FILTER(Budget, Budget[v] = _BN)
    )
VAR _BN_v03_amounts = CALCULATE([Total amounts], 
    FILTER(Budget, Budget[v] = _BN_v03)
    )
VAR _BN_v05_amounts = CALCULATE([Total amounts], 
    FILTER(Budget, Budget[v] = _BN_v05)
    )
VAR _BN_v07_amounts = CALCULATE([Total amounts], 
    FILTER(Budget, Budget[v] = _BN_v07)
    )
VAR _BN_v10_amounts = CALCULATE([Total amounts], 
    FILTER(Budget, Budget[v] = _BN_v10)
    )
VAR _current_amount = 
    IF(_current_month>=1 && _current_month<=3, _BN_amounts,
    IF(_current_month>=4 && _current_month<=5, _BN_v03_amounts,
    IF(_current_month>=6 && _current_month<=7, _BN_v05_amounts,
    IF(_current_month>=8 && _current_month<=9, _BN_v07_amounts,
    IF(_current_month>=10 && _current_month<=12, _BN_v10_amounts
    )))))

VAR result = _current_amount

RETURN 
    result

This expression works fine on a table 
but in a card it shows blank

The measure should react to the ofther fields in the table Budget.

Any help is appreciated thank you 🙂

[daXtreme]

Your measure can be compressed to this much more performant and easier to maintain form:

Amounts v =
VAR _current_month = MONTH( TODAY() )
VAR _current_year = MAX( Budget[Year] )
VAR FilterValue =
    CONCATENATE("B", _current_year)
    & switch( TRUE(),
        _current_month <= 3, "",
        _current_month <= 5, " v03",
        _current_month <= 7, " v05",
        _current_month <= 9, " v07",
        " v10"
    )
VAR Result =
    CALCULATE(
        [Total amounts],
        KEEPFILTERS(
            Budget[v] = FilterValue
        )
    )
RETURN
    Result

When it comes to the blank card... I'm afraid you'll have to give a link to a file that demonstrates the issue.

[Anonymous]

Thank you for your reply 

This is a simplification about the data that I have 

Envelope ID	Amount	version
ENV0001	15000	B2023
ENV0001	17000	B2023 v3
ENV0001	154000	B2023 v5
ENV0001	194421	B2023 v7
ENV0001	202345	B2023 v10

for every envelope, I have many versions of the budget, because every few months there is an estimation and each version corresponds to a month in the year, for example for the year 2023 I have a version for every period, let's say now that we are in october the sum should consider only  "v10", if we are n january 2023, the sum will consider the row flagged "B2023"

This logic as I implemented it works fine in a table but when I place it in a card, it shows a blank. 

I hope this clarifies more. Thank you

[daXtreme · Accepted solution]

Code like this:

VAR _current_amount = 
    IF(_current_month>=1 && _current_month<=3, _BN_amounts,
    IF(_current_month>=4 && _current_month<=5, _BN_v03_amounts,
    IF(_current_month>=6 && _current_month<=7, _BN_v05_amounts,
    IF(_current_month>=8 && _current_month<=9, _BN_v07_amounts,
    IF(_current_month>=10 && _current_month<=12, _BN_v10_amounts
    )))))

should be written like this:

VAR _current_amount = 
    switch( true(),
        _current_month <= 3, _BN_amounts,
        _current_month <= 5, _BN_v03_amounts,
        _current_month <= 7, _BN_v05_amounts,
        _current_month <= 9, _BN_v07_amounts,
        _BN_v10_amounts
    )

It's cleaner and easier to type/read.

In addition, code like this:

VAR _BN_amounts = CALCULATE([Total amounts], 
    FILTER(Budget, Budget[v] = _BN)
    )

is highly inefficient and should be written like this:

VAR _BN_amounts = 
    CALCULATE(
        [Total Amount],
        KEEPFILTERS( Budget[v] = _BN )
    )

Lastly... I don't quite get your point because there's no data to play with. Please supply a link to a file with some sample data to demonstrate the issue. The file can reside on any shared drive. Please don't forget to grant public access to  the file.

[Anonymous]

Thank you for your reply 

This is a simplification about the data that I have 

Envelope ID	Amount	version
ENV0001	15000	B2023
ENV0001	17000	B2023 v3
ENV0001	154000	B2023 v5
ENV0001	194421	B2023 v7
ENV0001	202345	B2023 v10

for every envelope, I have many versions of the budget, because every few months there is an estimation and each version corresponds to a month in the year, for example for the year 2023 I have a version for every period, let's say now that we are in october the sum should consider only  "v10", if we are n january 2023, the sum will consider the row flagged "B2023"

This logic as I implemented it works fine in a table but when I place it in a card, it shows a blank. 

I hope this clarifies more. Thank you