Forum Discussion
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.... 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!!!
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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
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
Hi, sorry. I don`t understand your post. All the language is new for me.
Can I fix using a expression?
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
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!!
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)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!!!
