Get average of Outstanding for last 3 months excluding the selected month .

[Samarth-Borade]

I have a table Contract No , in power bi , now in that i have columns "Month" , which is just the month number , "Outstanding" , so in "Month" slicer if i select one month for example 10 that is October , then i want the Average of Outstanding of last three months excluding October , so here it will be Outstanding of (July + August + September)/3 . Write a Dax measure for this.

Measure:

Average_3 =
VAR PreviousMonth1 = SELECTEDVALUE('Contract No'[Month]) - 1
VAR PreviousMonth2 = SELECTEDVALUE('Contract No'[Month]) - 2
VAR PreviousMonth3 = SELECTEDVALUE('Contract No'[Month]) - 3
RETURN
(
CALCULATE(
SUM('Contract No'[Outstanding]),
'Contract No'[Month] = PreviousMonth1
) +
CALCULATE(
SUM('Contract No'[Outstanding]),
'Contract No'[Month] = PreviousMonth2
) +
CALCULATE(
SUM('Contract No'[Outstanding]),
'Contract No'[Month] = PreviousMonth3
)
) / 3

When i drag this in a Card it shows Blank , even though there is data it shows Blank .

[amitchandak · Accepted solution]

@Samarth-Borade ,make sure you use date table in such cases

try like

Rolling 3 before current = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-1) ,-3,MONTH))

Rolling 3  before current = CALCULATE([Net], WINDOW(-4,REL, -1, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.