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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Lanceometer
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
Sahir_Maharaj
Super User
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? (Yes, its FREE!)
➤ 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
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

2 REPLIES 2
Lanceometer
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
)

RETURN
CALCULATE(
SUM(FACTS[QUANTITY])
, _inLast12Months
)
 
Anyway your solutuion seems to be more efficient
Sahir_Maharaj
Super User
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? (Yes, its FREE!)
➤ 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
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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