Need a DAX calc for Year-To-Date for the last non-zero value

[Anonymous]

I need some DAX help as I am just learning it. I have a Date table caled "Date" and a table called Fact_Vol which has values aggregated like this from the following SQL

SELECT
DateKey,
SUM(NET_REV) AS TotalRev
FROM dbo.Fact_Vol
WHERE DateKey >= 20191001
GROUP BY DateKey

DateKey TotalRev
20191001 3594818971.10521
20191101 3257245599.0963
20191201 3411774368.68592
20200101 3010738311.89645
20200201 0
20200301 0
20200401 0
etc

I would like to find the last non-zero revenue value, get the Date for that and give me the YTD value. In this example the last value for Total Revenue is for Jan 2020 so I would like the measure to give that value. When Feb is populated I would like it to be the Revenue for Jan+Feb, etc. 

I think I am close - I can isolate the standalone Date using this DAX. Just need to know how to get YTD from this date

VAR MAXDATE =

CALCULATE (

MAX ( 'Date'[Date] ),

FILTER (

ALL ( 'Date' ),

'Date'[Date] <= MAX ( 'Date'[Date] )

&& 'Fact_Vol'[Total Net Revenue] <> 0

))

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

Try, something like this

calculate(sum(Fact_Vol [NET_REV]),filter('Date','Date'[DateKey] >=20191001))

[Anonymous]

Thanks...I should say that I need it to be dynamic. I cant have hardcoded values in the calc. I want it to automatically work when Feb values get loaded. Otherwise I would have to go into the calc and change it each month

[Anonymous · Accepted solution]

Amit's solution did get me thinking of a modification however. This modified solution seems to work

NetRevenueYTD =
VAR MAXDATE =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] <= MAX ( 'Date'[Date] )
                && 'Fact_MACO'[Total Net Revenue] <> 0
        )
    )
RETURN
    CALCULATE (
        SUM ( Fact_MACO[Net Revenue] ),
        FILTER ( 'Date', 'Date'[Date] >= MAXDATE )
    )