cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

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
))
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 )
)

4 REPLIES 4
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Try, something like this

``calculate(sum(Fact_Vol [NET_REV]),filter('Date','Date'[DateKey] >=20191001))``
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Anonymous
Not applicable

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
Not applicable

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 )
)

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors