Help with Expression

[antoniodneto]

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.... 2007, is my contract date bill (when my customer really paid me).

On my KPI I want to display my sell value using my DATE_SELL, for example if I choose 2007 on my DATE filter, the KPI should return me on values on 2007 DATE_SELL.

Follow an example:

date	date_sell	value
2001	2002	500
2002	2002	300
2003	2003	100
2004	2005	110
2005	2005	200
2006	2007	300
2007	2007	600

In my case when I select 2007 on DATE it must return me all values into 2007 on DATE_SELL, 300+600.

But when I select 2007 my KPI only return me 600. How can I fix it?

Tks guys!!!

[Anonymous]

Hi @antoniodneto 

I would create a new measure as

_KPI = CALCULATE(SUM('table1'[VALUE]),ALLEXCEPT('table1','table1'[date_sell]))

and use it to display the KPI values

[harshnathani]

Hi @antoniodneto ,

Try this measure

KPI =
IF (
    MAX ( 'Table'[date] )
        IN VALUES ( 'Table'[date_sell] ),
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date_sell]
                = MAX ( 'Table'[date_sell] )
        )
    ),
    0
)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[antoniodneto]

Hi, IT WORKED!!!

Just one thing, when I select month by month return me correct. But when not month is selected it shows me the value for max date_sell, in this case should accumulated. Can I add that?

Tks a lot!!!

[harshnathani]

Hi @antoniodneto ,

Not very clear , but it accumulates the value of the max_date sell.

I think this is what you want.

Let me know if this help. 

Also, pls accept as solution for other community members to benefit.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[antoniodneto]

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 · Accepted solution]

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]

Hi, Didn`t work.

Don`t matter wich month is selected, it returns the same value. Looks like when I select the month it accumulated all values for date_sell.

Tks!!

[amitchandak]

@antoniodneto , not very clear.  You can join both dates with the same date table. One join will active another will be inactive join. You can activate join using userelation 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[antoniodneto]

Hi, sorry. I don`t understand your post. All the language is new for me.

Can I fix using a expression?