cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors