Forum Discussion

antoniodneto's avatar
antoniodneto
Regular Visitor
6 years ago
Solved

Help with Expression

Hi guys, I`m new with Power BI, can you help me?   I have one master filter on my dash called DATE, with 2001,2002..... 2007. is my contract date. And another called DATE_SELL with 2001,2002.... ...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi antoniodneto ,

    You can create this measure:

    Measure =
VAR _max =
    CALCULATE ( MAX ( 'Table'[date] ), ALLSELECTED ( 'Table'[date] ) )
RETURN
    IF (
        ISFILTERED ( 'Table'[date] ),
        IF (
            _max IN DISTINCT ( 'Table'[date_sell] ),
            CALCULATE (
                SUM ( 'Table'[value] ),
                FILTER ( ALL ( 'Table' ), 'Table'[date_sell] = MAX ( 'Table'[date_sell] ) )
            )
        ),
        SUM ( 'Table'[value] )
    )

    no date is selectedselected date

     

    Attached a sample file that hopes to help you: Help with Expression.pbix

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

antoniodneto's avatar
antoniodneto
Regular Visitor
6 years ago

So, when I select month by month the expression works. When I dont select any month the value is the same if I select 2007 (my max date). I would like to accumulate all the values for DATE_SELL when I don`t select any month.

 

Sorry if my explanation is not clearly.

v-yingjl's avatar
v-yingjl
Community Support
6 years ago

Hi antoniodneto ,

You can create this measure:

Measure =
VAR _max =
    CALCULATE ( MAX ( 'Table'[date] ), ALLSELECTED ( 'Table'[date] ) )
RETURN
    IF (
        ISFILTERED ( 'Table'[date] ),
        IF (
            _max IN DISTINCT ( 'Table'[date_sell] ),
            CALCULATE (
                SUM ( 'Table'[value] ),
                FILTER ( ALL ( 'Table' ), 'Table'[date_sell] = MAX ( 'Table'[date_sell] ) )
            )
        ),
        SUM ( 'Table'[value] )
    )

no date is selectedselected date

 

Attached a sample file that hopes to help you: Help with Expression.pbix

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.