12 Year Min/Max for running total

[kirbynguyen]

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

[kirbynguyen · Accepted solution]

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

[v-sdhruv]

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!

[Ilgar_Zarbali]

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.

[FBergamaschi]

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

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...