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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mariana_carmof
Frequent Visitor

Sum value in previous day that goal is not blank

Hi guys! 

I want to sum the quantity sold on a day that my target is blank to the quantity sold on a day immediately before that target has a value. 
The expected result is: 

mariana_carmof_0-1659467729245.png

Can anyone help me to do this using DAX?

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

If you have a Date table linked to both sales and targets then I think the below should work

Sales amended =
IF (
    NOT ISBLANK ( SUM ( Targets[Goal] ) ),
    VAR currentDate =
        SELECTEDVALUE ( 'Date'[Date] )
    VAR nextNonBlank =
        CALCULATE (
            FIRSTNONBLANK ( 'Date'[Date], SUM ( Targets[Goal] ) ),
            'Date'[Date] > currentDate
        )
    RETURN
        CALCULATE (
            SUM ( Sales[Sales made] ),
            DATESBETWEEN ( 'Date'[Date], currentDate, nextNonBlank - 1 )
        )
)

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

If you have a Date table linked to both sales and targets then I think the below should work

Sales amended =
IF (
    NOT ISBLANK ( SUM ( Targets[Goal] ) ),
    VAR currentDate =
        SELECTEDVALUE ( 'Date'[Date] )
    VAR nextNonBlank =
        CALCULATE (
            FIRSTNONBLANK ( 'Date'[Date], SUM ( Targets[Goal] ) ),
            'Date'[Date] > currentDate
        )
    RETURN
        CALCULATE (
            SUM ( Sales[Sales made] ),
            DATESBETWEEN ( 'Date'[Date], currentDate, nextNonBlank - 1 )
        )
)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors