The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a simple table with some sales day per day named Fact_Sales and a simple and normal calendar table named Dim_Calendar.
And I have a measure that is a simple SUM(Fact_Sales[Sales]) and a measure that calculate the % variation of sales month over month.
If I put in a visual table the column Dim_Calendar[Month/Year] and the measure SUM(Fact_Sales[Sales]) and the measure % Variation,we will see the months and the total of sales in this months and the % month over month, right?
I need to create a measure that calculate an AVERAGE value of last 12 months % Variaton, and use this average to predict the next 12 months of Sales, using the sales of last month.
With the following example, it becomes more clear to understand.
In column D, you can see the formula to calculate the value in blue (column C).
There is an curious fact, the months mar/23 until jan/24 doesn't exists in my table Dim_Calendar, feb/22 exists, but we need to predict too because the month is not complete yet.
We use Sales of jan/23 because it is the MAX complete month until today.
I need to use this measure in a simple card to show the value $ 52.280,18 and it need to be a MEASURE, because I have some slicers and filters in my page and the values must accept the filters, it cant be a fixed table in Power Query or something.
Ill follow up tomorrow when at work, I did this. But to get you started, you need to first get the average by summarizing the variation by month.
Measure =
VAR summaryTable = SUMMARIZECOLUMNS(Month, "Variation", {measure you created])
RETURN AVERAGEX(summaryTable,[Variation])
User | Count |
---|---|
77 | |
75 | |
36 | |
31 | |
29 |
User | Count |
---|---|
94 | |
80 | |
55 | |
48 | |
48 |