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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
andreas789
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?

andreas789_0-1650360698688.png

 

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

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

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
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

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?

hectormk22_0-1686942274575.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.