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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gpl
Frequent Visitor

Subtract todays total from previous day total if current month

Hey yall, thanks for all the help you guys provide! Hopefully someone can tell me how to do this as I have been struggling to figure this one out. I have a table that increments sales for the month and I am trying to get daily by subracting from previous day. I also don't want it to subtract if previous day is in a different month.

 

What I have:

Date          Sales

8/28/2021 91000

8/29/2021 91500

8/30/2021 91700

8/21/2021 92200

9/1/2021   450

 

What I want:

What I Have

Date          Sales     Daily Sales

8/28/2021 91000   

8/29/2021 91500   500

8/30/2021 91700   200

8/21/2021 92200   500

9/1/2021   450       450

 

Hopefully someone can help and I appreciate all the great resources here!

 

 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@gpl  Can you try the following which sums on the subtotal level as well

 

 

dailySales = 
SUMX (
    'fact',
    VAR _prevDate =
        MAXX (
            FILTER ( ALL ( 'fact'[Date] ), 'fact'[Date] < EARLIER ( 'fact'[Date] ) ),
            'fact'[Date]
        )
    VAR _prevMonth =
        MONTH ( _prevDate )
    VAR _currentDate =
        CALCULATE ( MAX ( 'fact'[Date] ) )
    VAR _currentMonth =
        MONTH ( _currentDate )
    VAR _prevDateSale =
        MAXX ( FILTER ( ALL ( 'fact' ), 'fact'[Date] = _prevDate ), 'fact'[Sales] )
    VAR _currentDateSale =
        CALCULATE ( SUM ( 'fact'[Sales] ) )
    VAR _diff =
        SWITCH (
            TRUE (),
            _prevMonth = BLANK (), BLANK (),
            _currentMonth = _prevMonth, _prevDateSale - _currentDateSale,
            _currentDateSale
        )
    RETURN
        IF ( _diff < 0, ABS ( _diff ), _diff + 0 )
)

 

 

smpa01_0-1632506050882.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

@gpl  Can you try the following which sums on the subtotal level as well

 

 

dailySales = 
SUMX (
    'fact',
    VAR _prevDate =
        MAXX (
            FILTER ( ALL ( 'fact'[Date] ), 'fact'[Date] < EARLIER ( 'fact'[Date] ) ),
            'fact'[Date]
        )
    VAR _prevMonth =
        MONTH ( _prevDate )
    VAR _currentDate =
        CALCULATE ( MAX ( 'fact'[Date] ) )
    VAR _currentMonth =
        MONTH ( _currentDate )
    VAR _prevDateSale =
        MAXX ( FILTER ( ALL ( 'fact' ), 'fact'[Date] = _prevDate ), 'fact'[Sales] )
    VAR _currentDateSale =
        CALCULATE ( SUM ( 'fact'[Sales] ) )
    VAR _diff =
        SWITCH (
            TRUE (),
            _prevMonth = BLANK (), BLANK (),
            _currentMonth = _prevMonth, _prevDateSale - _currentDateSale,
            _currentDateSale
        )
    RETURN
        IF ( _diff < 0, ABS ( _diff ), _diff + 0 )
)

 

 

smpa01_0-1632506050882.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
gpl
Frequent Visitor

Your a genius; is it possible if I put that measure in a table to get it to sum just those values? It is summing for TotalSales. Ideally the table to the right would show $14249

 

gpl_0-1632502470148.png

 

 

 

DataInsights
Super User
Super User

@gpl,

 

Try these measures:

 

Total Sales = SUM ( Table1[Sales] )

Daily Sales = 
VAR vDate =
    MAX ( Table1[Date] )
VAR vMinDate =
    CALCULATE ( MIN ( Table1[Date] ), ALLSELECTED ( Table1[Date] ) )
VAR vCurrentMonth =
    MONTH ( vDate )
VAR vCurrentAmount = [Total Sales]
VAR vPrevAmount =
    CALCULATE (
        [Total Sales],
        Table1[Date] = vDate - 1,
        MONTH ( Table1[Date] ) = vCurrentMonth
    )
VAR vResult =
    SWITCH (
        TRUE (),
        vDate = vMinDate, BLANK (),
        ISBLANK ( vPrevAmount ), vCurrentAmount,
        vCurrentAmount - vPrevAmount
    )
RETURN
    vResult

 

DataInsights_0-1632491693070.png

I assumed the 8/21/2021 date should be 8/31/2021.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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