Date when a cumulative total measure becomes positive

[23Steve32]

Hi,

I have a table and I want to find the earliest date when a cumulative measure becomes positive. I've created a measure which gets the minumim date, but when aggregated gets the minimum date overall. See the table below: 

You can see the date in August is correct but the total date is in March. I understand why it is getting this date, but I want it to display 4/8/2017. How do I do this? 

**Edit: For clarity - I just want to find the date the cumulative sum became positive for use in a datediff calculation later

Many thanks in advance

[v-jiascu-msft]

Hi @23Steve32,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[Greg_Deckler]

I am thinking that perhaps you need a calendar table and then a calculated column in that table that essentially does an IF statement and IF the measure (based upon the date) is negative, put a 0 in the column, otherwise a 1. Then you should be able to get the MIN of the date in that table?

[23Steve32]

I have a date table, the problem with that approach is the image I shared is one project, there are perhaps 200+ that I need to do this for, so I assume your method wouldnt work? 

[v-jiascu-msft]

Hi @23Steve32,

Can you share a sample please? I think you need the function SUMMARIZE to get the right answer.

Best Regards!

Dale

[23Steve32]

 Hi @v-jiascu-msft,

I've createrd a sample file, removing the sensative data, however I cannot seem to attach the file! Probably a major oversight on my behalf but do you know how I'd do this?

Thanks,
Steve

[Greg_Deckler]

Post the file on OneDrive or Box and create a share link.

[23Steve32]

Hi,

PLease find the file in the link. I've included 2 projects in the sample to give a feel for the kind of data
https://www.dropbox.com/s/06kyyi34ics47um/Cash%20flow%20sample.pbix?dl=0

Thanks,

Steve

[v-jiascu-msft · Accepted solution]

Hi @23Steve32,

Try this formula please. Please note the date format of mine is different from yours. You can check it out in this file.

Date cashflow becomes positive 2 =
VAR cumulation =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                'Append1',
                Append1[TLJOBCDE],
                'Date'[Date].[Year],
                'Date'[Date].[MonthNo]
            ),
            "value", [Cumulative Cash Flow]
        ),
        [value] >= 0
    )
VAR targetYear =
    MINX ( cumulation, [Year] )
VAR targetMonth =
    MINX ( cumulation, [MonthNo] )
RETURN
    CALCULATE (
        MIN ( 'Append1'[THDATEPY] ),
        YEAR ( Append1[THDATEPY] ) = targetYear
            && MONTH ( Append1[THDATEPY] ) = targetMonth
    )

Best Regards,

Dale

[23Steve32]

Sorry for the very late reply I've been super busy! Thank you so much for the help!