Get last 3 month sales

[Hvaldes]

Dear Community

Very good night

I would like to ask for your cooperation to get a measure.

I need to get the sale of the last 3 months excluding the current one.

I've searched for a lot of information but I can't figure out where I can fail to create such a measure.

Here's the syntax

Last_3_Months ? CALCULATE(SUM('Billing MRS'[Sales]),DATESINPERIOD('Mrs Billing'[DocDueDate],LASTDATE('Invoicing MRS'[DocDueDate]),-3,MONTH))

DocDueDate is the date variable in the Billing table

Thanks a lot

[v-xicai · Accepted solution]

Hi @Hvaldes ,

You may create a calendar table firstly, then create relationship between the fact data table and the calendar table on date field.

Calendar= CALENDARAUTO()

Then you may create measure like DAX below.

Last_3_Month sales =

VAR _LastDate =

    LASTDATE ( 'Billing MRS'[Date] )

RETURN

    CALCULATE (

        SUM ( 'Billing MRS'[Sales] ),

        FILTER (

            ALLSELECTED ( 'Billing MRS'),

            Calendar[Date] < LastDate_

                && Calendar[Date] >= DATEADD ( _LastDate, -4, MONTH )

        )

    )

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Hvaldes , Try like this with a date table

Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[harshnathani]

Hi @Hvaldes ,

You will need a Calendar Table.

Try this measure

Last_3_Months =
CALCULATE (
    SUM ( 'Billing MRS'[Sales] ),
    DATESBETWEEN (
        'Calendar'[Date],
        EDATE (
            MIN ( 'Calendar'[Date] ),
            -3
        ),
        MIN ( 'Calendar'[Date] ) - 1
    )
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!