cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge. Frequent Visitor

## Calculate total sales for the last period

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``````

1 ACCEPTED SOLUTION  Community Support

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

4 REPLIES 4  Community Support

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 Frequent Visitor

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'.  Community Support

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 New Member

Hi, Is there any way to calculate the amount of the LP, for each period?  Announcements #### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features. #### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator. #### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (4,373)