Rolling 12 months every quarter

[ktt777]

Hi

How can i create a graph showing average of Price :

2023Q4 : Average of price ( remove those with price less than 0) of all record from jan-dec 2023

2024Q1: Average of price ( remove those with price less than 0) of all record from Apr 2023-Mar 2024

2024Q2: Average of price ( remove those with price less than 0) of all record from Jul 2023- 2024 Jun 2024

2024Q3 :Average of price ( remove those with price less than 0) of all record from Oct 2023-Sep 2024

2024Q4: Average of price ( remove those with price less than 0) of all record from Jan 2024-Dec 2024

thanks 

Here is the link to the sample file : Test.pbix

[Anonymous · Accepted solution]

Hi @ktt777,

I suggest you to Create a DimDate table to help calculation.

DimDate =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "Qn",
        "Q" & ""
            & QUARTER ( [Date] ),
    "Month", MONTH ( [Date] ),
    "YearQuarter",
        YEAR ( [Date] ) * 100
            + QUARTER ( [Date] )
)

Then create an unrelated "X Axis" table.

X Axis = 
SUMMARIZE(DimDate,DimDate[Year],DimDate[Qn],DimDate[YearQuarter])

Measure:

Average =
VAR _ENDDATE =
    CALCULATE (
        MAX ( DimDate[Date] ),
        FILTER (
            ALLSELECTED ( DimDate ),
            DimDate[YearQuarter] = MAX ( 'X Axis'[YearQuarter] )
        )
    )
VAR _STARTDATE =
    EOMONTH ( _ENDDATE, -12 ) + 1
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Price] ),
        FILTER ( 'DimDate', DimDate[Date] >= _STARTDATE && DimDate[Date] <= _ENDDATE ),
        FILTER ( 'Table', 'Table'[Price] > 0 )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dangar332]

Hi, @ktt777 

try below measure and check attached pbix file
just adjust your table and column name

Measure 4 = 
var a = CALCULATE(
    AVERAGE(Sheet1[Price]),
    Sheet1[Column]=MAX(Sheet1[Column]),
    ALLEXCEPT(Sheet1,Sheet1[Column])
)
return
IF(
    MIN(Sheet1[Date].[QuarterNo])=4,
    a,
    AVERAGE(Sheet1[Price])
)

download file HERE 

f there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.