Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NorthernGuy
Frequent Visitor

Display breakeven date in a card

Hi,

 

I need to evaluate a column of numbers, Cash Flow, and return the Date listed in a seperate column, in a Card.  The month that I want to display in Card is the first month that the Cash Flow amount is positive.  

 

Date         /   Cash Flow

1/31/16    /  -100

2/28/16    /    -50

3/31/16    /     75

 

In the example above I want to be able to display the date, "3/31/16" in a Card?

 

Mark sense?  Sorry if this is easy, I've looked without success.  Thanks!

18 REPLIES 18
Greg_Deckler
Super User
Super User

I am thinking something like:

 

CALCULATE(MIN(Table[Date]),FILTER(Table, Table[Cash Flow] > 0))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler I have two questions:

 

First - what is the difference between

CALCULATE(MIN(Table[Date]),FILTER(Table, Table[Cash Flow] > 0))

and

 CALCULATE(MIN(Table[Date]),Table[Cash Flow] > 0


Second - I have following data:

 

 

close value data.png


I created following measure:

CALCULATE(MIN(Table1[Date]), FILTER(Table1, Table1[Count] > 11))

I expect '1/12/2015' to be returned but it is giving '1/1/2015'. How to achieve the desired result.

@javedbh, I'm with @KHorseman, the formula is providing the correct answer, but perhaps we didn't understand the real question?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Sorry, I apologize for my idiocy. I want to get the min date where count  = (min count where count > 11). In my example (min value of count column where count > 11) should return 12 and if there are two records against 12 then get min date. Hope I explained my question well. Smiley Happy

So you don't want the earliest date when the value was over 11, you want the most recent date when the value was over 11 and the previous value was not over 11. In other words you want the most recent crossover, not the first breakeven.

 

I believe that will require both a calculated column and a measure. First the column:

 

PreviousCount = CALCULATE( MIN( Table[Count]), FILTER( ALL( Table), Table[Date] = EARLIER(Table[Date]) - 1))

 

Then the measure is

 

Most Recent Breakeven = CALCULATE( MAX( Table[Date]), FILTER( Table, Table[Count] > 11 && Table[PreviousCount] <= 11))





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




 

First Create a Calculated Column:

 

Columna = IF('Table'[Count]-11<1;99999999999999999999999999999999;'Table'[Count]-11)

 

Next Create a Measure

 

Medida = CALCULATE(Min('Table'[Date]);FILTER('Table';'Table'[Columna]=MIN('Table'[Columna])))

 

 




Lima - Peru

@Vvelarde I'm not sure I understand what you're doing there. First of all that measure will just return the first date where the count was the lowest it's ever been, which I don't think is what @javedbh was asking for. And your measure doesn't ever reference your new calculated column, so what was the column for?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yeah, sorry was a bad copy and paste; i update my reply. Maybe is not the best answer but i think it works.




Lima - Peru

Have you tested my method? It returns the most recent date when the Count was less than 11 but the Count on the previous day was not less than 11.

 

I still don't understand what your calculated column is meant to accomplish. First date where the Count was less than 11 and not less than an approximation of 2? Also I think you're missing the "else" argument in that IF statement, so that formula will probably just return an error. I'm not trying to nitpick, just trying to follow the logic.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @KHorseman ; your method it works.

 

The trick is in the question;

 

He want the first time when count arrives to 12 and previous date was minor to 12.

Or the first date when count passes ot equal to 12  Without distinction that is coming to 10 to 12 or 14 to 12.

 

This is your method and also my simple and Newbie method. (Sorry for my basic english)

Sin título.png

 

 

 

 




Lima - Peru

@KHorseman Your method worked well with the data I posted originally. But when I added dates in random order or Count with different values then your method failed a few times.

 

@VvelardeYou method also worked well and produced correct results where @KHorseman method failed.

 

Still I wanted a better way to accomplish this without the need of a computed column.

 

In sql it would be like this:

SELECT min(d.Date) FROM Table_1 d WHERE d.Count = (SELECT min(t.Count) FROM Table_1 t WHERE t.Count > 11)

So my equivalent of this sql query is:

CALCULATE(Min(Table1[Date]), FILTER(Table1,Table1[Count]=CALCULATE(MIN(Table1[Count]), FILTER(Table1,Table1[Count]>11))))

If that's your formula then I must not understand what you're asking for. Your formula should return the earliest date when the count was the lowest recorded value greater than 11. So for instance if you have two dates where the count was 12, I think your formula will give you the earlier of those two dates. Is that what you want? What I thought you were asking for was the most recent date when the count rose past 11. So if you had a count of 12 a month ago then dipped back down below 11 for several weeks and then came back up over 11 two days ago, my method would give you the date from two days ago.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Vvelarde @KHorseman Thanks to both of you for your time and effort. It is very encouraging to see people helping other people.

Vvelarde
Community Champion
Community Champion

Maybe creating a calculated column that rest column (Count -11), and extract the top 1 sorted for this column.

 




Lima - Peru

1/1/2015 is the earliest date in that list where the Count is > 11. It's 25 on that date. Why do you expect 1/12/2015?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler yeah that might be more stable. I suppose it depends on the structure of the table a bit. My choice between MIN and FIRSTNONBLANK is usually sort of a semantic shorthand for how I'm mentally modeling the data. MIN is for cases where I have a bunch of values and I want the smallest one no matter where it occurs and FIRSTNONBLANK is for cases where I want to pull a single row's value from a well-ordered set. So I guess my suggestion is making some assumptions about the data model.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks guys,

 

This works and helpful -- I went with the Min approach --  Thanks to all.

 

Cheers.

KHorseman
Community Champion
Community Champion

I think this will do it...

 

Breakeven Date = CALCULATE( FIRSTNONBLANK( Table[Date]), FILTER(Table, Table[Cash Flow] > 0))

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.