How to calculate the last 3 months results from the date selected on slicer?

[AIDA_User]

Want to be able to select a month on a slicer, then the measure caculates the last 3 months sales from the month selected. So for example if I selected Feb 24 on the slicer, I want to see sales from November 2023 to January 2024.

Any ideas? 

Have been using the below which worked when looking at the last date of sales, but now we want to go back and look at previous months to make comparisons.  

Sales L3months = CALCULATE(

    SUM(Customer_Sales[Invoice_Quantity]),

    DATESINPERIOD(

        Customer_Sales[Invoice_Date],

        LASTDATE(Customer_Sales[Invoice_Date]),

        -3,

        MONTH)

)

@ https://radacad.com/calculate-totals-in-power-bi-using-iterators-in-dax

Power BI Helper Tool by RADACAD 

@amitchandak 

@Ashish_Mathur 

@EnterpriseDNA 
@Fowmy 

@dufoq3 

@lbendlin @ronrsnfld 

[Ajithkumar_03]

Hi @AIDA_User ,

Step 1 :
In date table to create previous month column.

Step 2 :
Create duplicate date table to relate with date table then create the below measure.

Result:
If I select any month in slicer, values showing from previous month to last 3 month sales.

Best Regards,
Ajith Kumar

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

[AIDA_User]

Hi Ajith

I have been working through each suggestion and so far no good.  I have now followed your steps and the below error has come up, please note I followed these steps:

1. Created your suggested new column PreviousMonth in the existing table
2. Created a duplicate of the table (the entire table as our dates are in that table, dont have a seperate dates table as our table has all customer sales (products/ skus, invoice numbers, time / dates) along with customer details). Dont know if I can just pull the dates out?
3. In the duplicate table I created your measure (was I meant to create the measure in the original table where I created the new column? Assumed to create the new measure in the duplicate one?)
4. When this didnt work, I then created a relationship between the two tables, first on dates but it didnt fix anything, so i deleted the relationship and then created a relationship on the customer ID/ code. Still did not fix the error. 

Any ideas? 

[Ajithkumar_03]

Hi @AIDA_User ,

Please create duplicate date table and create inactive relation between date to duplicate date table and use userelationship between both table. please refer my DAX.



Regards,
AjithKumar

[AIDA_User]

Hi there, 

I have done this. Still having issues. I have created a date table with inactive relationship. Still haveing issue. 

[AIDA_User]

To confirm your results you go and the view is exactly what I am looking for. 

[Anonymous · Accepted solution]

Hi @AIDA_User ,

Have you tried Ashish's suggestions? 
I'm assuming your slicer is at the day level. Please create a new unjoined date table for the slicer and then create this measure:

Sales L3months = 
VAR __slicer_date = MAX('Date'[Date])
VAR __max_date = EOMONTH(__slicer_date,-1) + 1
VAR __min_date = EDATE(__max_date,-3)
VAR __result = CALCULATE(SUM('Customer_Sales'[Invoice_Quantity]),'Customer_Sales'[Invoice_Date]>=__min_date && 'Customer_Sales'[Invoice_Date]<__max_date)
RETURN
__result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[AIDA_User]

This worked! Thank you! Now when I select the month on the slicer, it brings me the last 3 months. 

Thank you 

[AIDA_User]

Hi there, thank you for your time and help. 

Confirming my slicer is at month level, just year and month in the slicer.

So I have created a second table, it is a duplicate of the 1st table, I dont have a separate table for dates.  All of our data, sales, customer, invoice, sku, dates are in one table.

So I created a second table named Customer_Sales (2). 

Then I went back to the original table Customer_Sales and created this measure: 

Sales L3months =

VAR __slicer_date = MAX('Customer_Sales (2)'[Invoice_Date].[Date])

VAR __max_date = EOMONTH(__slicer_date,-1) + 1

VAR __min_date = EDATE(__max_date,-3)

VAR __result = CALCULATE(SUM('Customer_Sales'[Invoice_Quantity]),'Customer_Sales'[Invoice_Date]>=__min_date && 'Customer_Sales'[Invoice_Date]<__max_date)

RETURN

__result

I then droped that measure into a table along side the Customer_Sales[invoice quanitity]. Nothing came up so then I created a slicer so I could select a month, I ended up creating a slicer from both tables but but no data was showing up. 

I then thought maybe I was meant to put the measure in the other table, so I created this measure in the 2nd table :

Sales L3months =

VAR __slicer_date = MAX('Customer_Sales (2)'[Invoice_Date].[Date])

VAR __max_date = EOMONTH(__slicer_date,-1) + 1

VAR __min_date = EDATE(__max_date,-3)

VAR __result = CALCULATE(SUM(Customer_Sales[Invoice_Quantity]),Customer_Sales[Invoice_Date]>=__min_date && 'Customer_Sales'[Invoice_Date]<__max_date)

RETURN

__result

Tried using both of the slicer from each table but nothing showed up? What am I doing wrong the measures seem like they should work?

[Ashish_Mathur]

Hi,

I am n ot very sure of what you want.  Try this

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Invoice Date column of the Customer Sales table to the Date column of the Calendar Table
3. To your slicer, drag Year and Month name from the Calendar Table.  Select a month there
4. Write these measures

Total = SUM(Customer_Sales[Invoice_Quantity])

Sales in 3 months ended = calculate([Total],datesbetween(calendar[date],edate(min(calendar[date]),-2),max(calendar[date])))

Hope this helps.