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

The 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.

Reply
superhayan
Helper I
Helper I

Average, Max, Min of a measure by month

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.

superhayan_0-1709855111029.png

 

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!!!

 

 

 

1 REPLY 1
lbendlin
Super User
Super User

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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