cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Quantity of the last 12 months

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"

1 ACCEPTED SOLUTION
Super User

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
)
``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
2 REPLIES 2
Regular Visitor

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)RETURNCALCULATE( SUM(FACTS[QUANTITY]) , _inLast12Months)`

Anyway your solutuion seems to be more efficient
Super User

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
)
``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors