cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 )
    )

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

Try, something like this

calculate(sum(Fact_Vol [NET_REV]),filter('Date','Date'[DateKey] >=20191001))
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 )
    )

Helpful resources

Announcements
Microsoft Build 768x460.png

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.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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