Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello all, I have to do a forecast analysis and I am stuck in getting the monthly average, max and min of a measure.
To forecast for a particular month, I need to use the snapshots of forecast from 15 days before the month to 15 days after that month (e.g. to forecast for Feb, I need to use snapshots taken from 16 Jan till 15 Mar). The number of snapshots are different for each month and I have built an "In-Month" to check whether the snapshots fall into that period. When this column is "In Month", then this data falls into the period but if it is blank, then I don't need to count this data in.
As each snapshot is a forecast of an individual customer's revenue only, I need to add up all the $ of the same snapshot date which is "In Month" to give a daily sum first. Then I need to find the monthly average, and the max daily sum and min daily sum of the forecast month (not the snapshot month). For instance, if March has 4 "In Month" snapshot dates as below:
Forecast from 28 Feb daily sum: $100
Forecast from 1 Mar daily sum: $200
Forecast from 18 Mar daily sum: $300
Forecast from 8 Apr daily sum: $400
Then March monthly average = $250, monthly max = $400, monthly min = $100.
My ultimate goal is to build a line chart with Forecast Month (Forecast_M in the screen capture) as x axis, and 3 lines showing average forecast, max daily forecast and min daily forecast for each month. Im stuck in this for hours but can only get weird figures.... could anyone give me some insight? Thank you!!!
You cannot measure a measure. You need to materialize it first, with SUMMARIZE or SUMMARIZECOLUMNS (or with Visual Calculations if all your data is already in a visual).
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
22 | |
17 | |
17 | |
11 | |
7 |
User | Count |
---|---|
27 | |
26 | |
13 | |
12 | |
12 |