topic Re: Quantity of the last 12 months in DAX Commands and Tips

Quantity of the last 12 months

Lanceometer — Fri, 12 Apr 2024 14:08:30 GMT

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"

Re: Quantity of the last 12 months

Sahir_Maharaj — Fri, 12 Apr 2024 20:14:08 GMT

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 )

Re: Quantity of the last 12 months

Lanceometer — Sat, 13 Apr 2024 11:14:46 GMT

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