Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
@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 )
)
@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 )
)
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,
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
I assumed the 8/21/2021 date should be 8/31/2021.
Proud to be a Super User!
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |