Quantity of the last 12 months

[Lanceometer]

Hi folks,

I want to create a Complaint ratio for customer complaints.

For that I want to take the amount of complaints and divide it by the average sales quantity per month of the last 12 months from the latest selected month.

But I am already failing in calculating the Quantity of the last 12 month (referring to the latest selected month)

I have a Fact Table:

FACT_DT, QUANTITY, PRODUCT_ID

2023-01-31,146,a

2023-02-28,546,a

2023-03-31,651,a

2023-04-30,713,a

2023-05-31,358,a

2023-01-31,444,b

2023-02-28,512,b

2023-03-31,43,b

2023-04-30,4525,b

2023-05-31,45,b

and so on

My Date Table is DIM_Calendar and has the Columns Date (e.g. "21.03.2023") and Month (e.g. "Mar 2023")

I have a Dimension Table for the Products

In my dashbard there is a slicer on the "Month" field of the DIM_Calendar table.

I have a bar chart with "Product" on the Y-axis.

On the X-axis it should show the quantity of the last 12 months from the latest selected month. so i select in my Slicer "Feb 2023" and "Mar 2023",

I want to see the sum of the quantity from "Apr 2022"+"Jun 2022"+"Jul 2022"+"Aug 2022"+"Sep 2022"+"Oct 2022"+"Nov 2022"+"Dec 2022"+"Jan 2023"+"Feb 2023"+"Mar 2023"

[Lanceometer]

Thank you @Sahir_Maharaj 

this looks great! Is there a reason to use EDATE instead of DATEADD ?

After a night of sleep I also found a solution.

But I have to add a column in my calendar table called [Twelve months before] and then go with

VAR _latestMonth = 
MAX(DIM_Calendar[EOM])

VAR _latestMonthMinus12 = 
MAX(DIM_Calendar[Twelve months before])

VAR _inLast12Months = 
FILTER(
ALL(DIM_Calendar)
, DIM_Calendar[Date] >= _latestMonthMinus12 && DIM_Calendar[Date] <= _latestMonth
)

RETURN
CALCULATE(
 SUM(FACTS[QUANTITY])
 , _inLast12Months
)

Anyway your solutuion seems to be more efficient

[Sahir_Maharaj · Accepted solution]

Hello @Lanceometer,

Can you please try the following:

Quantity Last 12 Months = 
VAR LatestMonthSelected = MAX('DIM_Calendar'[Date])
VAR StartDate = EDATE(LatestMonthSelected, -12)
RETURN
CALCULATE(
    SUM('FACT_DT'[QUANTITY]),
    'FACT_DT'[FACT_DT] > StartDate && 'FACT_DT'[FACT_DT] <= LatestMonthSelected
)