Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |