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
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"
Solved! Go to 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
)
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
)
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
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
16 | |
11 | |
10 | |
8 |
User | Count |
---|---|
62 | |
20 | |
11 | |
11 | |
11 |