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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hvaldes
Frequent Visitor

Get last 3 month sales

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

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
v-xicai
Community Support
Community Support

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
Super User
Super User

@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
Community Champion
Community Champion

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!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.