topic Re: 12 Year Min/Max for running total in DAX Commands and Tips

12 Year Min/Max for running total

kirbynguyen — Fri, 25 Jul 2025 14:37:43 GMT

Hello, I am using a running total measure to calculate the current month, the prev month, and the same month last year. The challenging part is creating the Range for the past 12 months. How can I calculate the 12 month min and the 12 month max?

Here are my measures for the current and previous month:

Current = VAR _day = SELECTEDVALUE(Dates2[Day Number]) VAR _sum = CALCULATE(SUM(Table[Volume]), Dates[Day Number] = _day) RETURN IF(ISBLANK(_sum), BLANK(), CALCULATE( SUM(Table[Volume]), FILTER( ALLSELECTED(Dates[Day Number]), ISONORAFTER(Dates[Day Number], MAX(Dates[Day Number]), DESC) && Dates[Day Number] <= _day ) )) LastMonth = VAR _day = SELECTEDVALUE(Dates2[Day Number]) VAR _month = SELECTEDVALUE(Dates[PrevMonthYearDate]) VAR _sum = CALCULATE(SUM(Table[Volume]), ALL(Dates), Dates[Day Number] = _day && Dates[MonthYearDate] = _month) RETURN IF(ISBLANK(_sum), BLANK(), CALCULATE( SUM(Table[Volume]), FILTER( ALL(Dates), ISONORAFTER(Dates[Day Number], MAX(Dates[Day Number]), DESC) && Dates[Day Number] <= _day && Dates[MonthYearDate] = _month ) ))

The dataset is simple. I use 3 tables, the Data table with Dates, Volumes, and Locations. The other 2 tables are Date tables. I use 2 date tables because the amount of days in the selected month may not be complete or will have less days than the previous month so the days after would not show.

(The yellow line after the end of the blue line won't show if I didn't use 2 date tables.)

Re: 12 Year Min/Max for running total

lbendlin — Fri, 25 Jul 2025 15:30:56 GMT

You can use the same formula for all five measures, by using a CALENDAR filter that covers the desired range (single months, or 12 months etc).

Obligatory note: Comparing days in months is suboptimal because these days will fall on different weekdays.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: 12 Year Min/Max for running total

FBergamaschi — Sat, 26 Jul 2025 18:42:17 GMT

You can use a single Calendar, we shall fix the yellow line issue (which I assume is due to autoexist)

What I am not sure to get is what you mean by Range. It is completely clear what you mean by current month, the prev month, and the same month last year, but the range part is a bit confusing to me

Do you want limit a graph to the last 12 month or do you want in a single measure to calculate the last 12 month volume?

As Ibendin suggested, please provide data and expected output of this Range 12 month measure

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: 12 Year Min/Max for running total

Ilgar_Zarbali — Mon, 28 Jul 2025 03:37:26 GMT

To calculate the Min and Max range for each day across the past 12 months, you can follow this approach:

Create two additional DAX measures:

12M_Min: Running total minimum for each day number across the past 12 months
12M_Max: Running total maximum for each day number across the past 12 months

Notes:

For each day (e.g., 1 to 31), calculate the cumulative sum up to that day for each of the past 12 months, then return the minimum and maximum from those 12 values.

12M_Min: DAX Measure

12M_Min = VAR _day = SELECTEDVALUE(Dates2[Day Number]) VAR _last12Months = DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -12, MONTH) RETURN MINX( VALUES(Dates[MonthYearDate]), CALCULATE( SUM(Table[Volume]), FILTER( ALL(Dates), Dates[Day Number] <= _day && Dates[MonthYearDate] = EARLIER(Dates[MonthYearDate]) ), _last12Months ) )

12M_Max: DAX Measure

12M_Max = VAR _day = SELECTEDVALUE(Dates2[Day Number]) VAR _last12Months = DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -12, MONTH) RETURN MAXX( VALUES(Dates[MonthYearDate]), CALCULATE( SUM(Table[Volume]), FILTER( ALL(Dates), Dates[Day Number] <= _day && Dates[MonthYearDate] = EARLIER(Dates[MonthYearDate]) ), _last12Months ) )

Notes:

These measures assume your Dates table has a unique MonthYearDate (e.g., formatted as "Jul 2025").

We use VALUES(Dates[MonthYearDate]) to iterate over each of the last 12 months.
The EARLIER function ensures that the filter context is respected during iteration.
These measures give you the shaded band (min-max range) like in your first chart.

To reproduce the shaded band:

Plot 12M_Min and 12M_Max as area charts or use the Ribbon chart in Power BI.
Overlay the Current, LastMonth, and SameMonthLastYear measures as lines for comparison.

If you found this helpful, please consider marking it as the accepted solution and giving it a thumbs up.

Re: 12 Year Min/Max for running total

v-sdhruv — Thu, 31 Jul 2025 06:30:49 GMT

Hi kirbynguyen ,

Just wanted to check if you had a chance to go through the suggestions provided by the super users?
Were you able to resolve the issue?
If not, please consider sharing a sample data for better understanding.
How to provide sample data

Thank You!

Re: 12 Year Min/Max for running total

kirbynguyen — Thu, 31 Jul 2025 13:43:20 GMT

The earlier reply wasn't what I wanted, but it gave me an idea. Here is the answer:

12M_Max = VAR _day = SELECTEDVALUE(Dates2[Day Number]) VAR _last12Months = DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -12, MONTH) VAR _month = SELECTEDVALUE(Dates[MonthYearDate]) RETURN MAXX( ADDCOLUMNS(SUMMARIZE(FILTER(ALL(Dates), Dates[Date] IN _last12Months), Dates[MonthYearDate]), "RunningTotal", CALCULATE( SUM(KPLER[Volume (bbl)]), FILTER( ALL(Dates), Dates[Day Number] <= _day && Dates[MonthYearDate] = EARLIER(Dates[MonthYearDate]) && Dates[MonthYearDate] < _month ) ) ), [RunningTotal])