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.
Hello,
I am trying to calculate the sales for the last n periods in n different measures.
I have the following custom calendar table. How can I create the measures that calculate the sales in the previous periods?
For the month -1,-2,...,-n, I've found the following measure that works. But I can't create a measure for period -1,-2,...-n.
Month - 1 QTY =
VAR _index = 1
VAR _StartMonth = DATE( YEAR( TODAY() ), MONTH( TODAY() ) - _index , 1)
VAR _StartNextMonth = EOMONTH( _StartMonth, 0) + 1
VAR _Amount = CALCULATE(SUM(l2y_sales[QUANTITY]) , periodsfulldata[PK_Date] >= _StartMonth, periodsfulldata[PK_Date] < _StartNextMonth)
RETURN _Amount
Solved! Go to Solution.
Hi, @andreas789
Please first make sure that the data type of your Column 'Period' is 'whole number'.
Then try formula as below:
Period1 =
VAR _period =
CALCULATE (
MAX ( periodsfulldata[Period] ),
FILTER ( periodsfulldata, periodsfulldata[PK_Date] = TODAY () )
)
VAR _lastperiod = _period - 1
VAR _StartNextWeek =
CALCULATE (
MAX ( periodsfulldata[PK_Date] ),
FILTER ( ALL ( periodsfulldata ), periodsfulldata[Period] = _lastperiod )
)
VAR _StartDate =
CALCULATE (
MIN ( periodsfulldata[PK_Date] ),
FILTER ( ALL ( periodsfulldata ), periodsfulldata[Period] = _lastperiod )
)
VAR _Amount =
CALCULATE (
SUM ( l2y_sales[QUANTITY] ),
periodsfulldata[PK_Date] >= _StartDate,
periodsfulldata[PK_Date] < _StartNextWeek
)
RETURN
_Amount
Best Regards,
Community Support Team _ Eason
Hi, @andreas789
If your 'period' corresponds to 'week', you can try formula as below:
Week - 1 QTY =
VAR _index = 1
VAR _StartDate =
TODAY () - WEEKDAY ( TODAY (), 2 ) - 7 * _index
VAR _StartNextWeek = _StartDate + 6
VAR _Amount =
CALCULATE (
SUM ( l2y_sales[QUANTITY] ),
periodsfulldata[PK_Date] >= _StartDate,
periodsfulldata[PK_Date] < _StartNextWeek
)
RETURN
_Amount
Best Regards,
Community Support Team _ Eason
Hey @v-easonf-msft, thanks for the reply but unfortuantelly this is not the case. I need to calculate the sales based on the column 'Period'.
Hi, @andreas789
Please first make sure that the data type of your Column 'Period' is 'whole number'.
Then try formula as below:
Period1 =
VAR _period =
CALCULATE (
MAX ( periodsfulldata[Period] ),
FILTER ( periodsfulldata, periodsfulldata[PK_Date] = TODAY () )
)
VAR _lastperiod = _period - 1
VAR _StartNextWeek =
CALCULATE (
MAX ( periodsfulldata[PK_Date] ),
FILTER ( ALL ( periodsfulldata ), periodsfulldata[Period] = _lastperiod )
)
VAR _StartDate =
CALCULATE (
MIN ( periodsfulldata[PK_Date] ),
FILTER ( ALL ( periodsfulldata ), periodsfulldata[Period] = _lastperiod )
)
VAR _Amount =
CALCULATE (
SUM ( l2y_sales[QUANTITY] ),
periodsfulldata[PK_Date] >= _StartDate,
periodsfulldata[PK_Date] < _StartNextWeek
)
RETURN
_Amount
Best Regards,
Community Support Team _ Eason
Hi, Is there any way to calculate the amount of the LP, for each period?
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 |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |