Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Average Running total each quarter interval

I need running total for each quarter interval DAX Formula result shown in the table "average running total" 2025-Q2 still running quarter show value as zero once this quarter is completed then aut...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Try this:

    Qtr Avg Running Total 3 = 
// Get latest visible date with data
VAR LatestDateWithData =
    CALCULATE (
        LASTNONBLANK ( Dates[Date], [Total Revenue] ),
        ALLSELECTED ( Dates )
    ) // Get earliest YearQuarter
VAR EarliestQtr =
    CALCULATE ( MIN ( Dates[YearQuarter] ), ALL ( Dates ) )
VAR LatestMonthNumber =
    MONTH ( LatestDateWithData ) // Get month number of latest date
VAR QuarterNumber4Start =
    EDATE (
        CALCULATE ( MINX ( STARTOFQUARTER ( Dates[Date] ), [Date] ), ALL ( Dates ) ),
        3 * 3
    )
VAR LatestYearQuarter =
    FORMAT ( LatestDateWithData, "YYYY0Q" ) // Get YearQuarter key of latest date
VAR RowQuarter =
    MAX ( Dates[YearQuarter] ) // Get YearQuarter key for current row
VAR QuarterEnds = { 3, 6, 9, 12 } // Quarter end months
VAR StartOfQtr =
    MINX ( STARTOFQUARTER ( Dates[Date] ), [Date] )
VAR StartOfPrevQtr = StartOfQtr - 1 // Day before start of current quarter
VAR _Result =
    IF (
        HASONEVALUE ( Dates[YearQuarter] ),
        SWITCH (
            TRUE (),
            StartOfQtr <= QuarterNumber4Start, 0, // Show 0 if earliest quarter
            LatestYearQuarter = RowQuarter
                && NOT LatestMonthNumber
                    IN QuarterEnds && ISFILTERED ( Dates[YearQuarter] ), 0, // Show 0 if latest incomplete quarter
            CALCULATE (
                AVERAGEX (
                    VALUES ( Dates[YearQuarter] ),
                    [Total Revenue]
                ),
                DATESINPERIOD ( Dates[Date], StartOfPrevQtr, -4, QUARTER ),
                REMOVEFILTERS ( Dates )
            )
        )
    )
RETURN
    _Result

     

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

You can change the RETURN statement to this:

RETURN
    IF (
        // Check if the current row is the latest (still running) quarter
        LatestYearQuarter = RowQuarter
            && NOT LatestMonthNumber
                IN QuarterEnds && ISFILTERED ( Dates[YearQuarter] ),
        0, // Show 0 for the latest incomplete quarter
        CALCULATE (
            // Calculate the running average up to the current quarter
            AVERAGEX ( VALUES ( Dates[YearQuarter] ), [Total Revenue] ),
            DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -4, QUARTER ),
            REMOVEFILTERS ( Dates )
        )
    )

REMOVEFILTERS ( Dates ) is not necessary if you have set your Dates/Calendar table as such. 

Set and use date tables in Power BI Desktop 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

danextian - It works as one thing not working for running current quarter this will make as zero if current quarter is 2025-Q2 still we have 18 days left.

 

another one help for we are giving average above 4 Quarter in current quarter incase doesn't have 4 quarter then value should be 0 (example as follows)

 

DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -4, QUARTER ))
 
example :   should be value (average total)
 
2023-01 -- 0 (zero)
2023-02 ---0 (above 1 Quarter so no average make for 4 Quarter so zero) 
2023-03 --- 0 (above 2 Quarter so no average make for 4 Quarter so zero)
2023-04 --- 0 (above 3 Quarter so no average make for 4 Quarter so zero)
2024-01  - as per formula (above 4 Quarter) - condition fulfill and DAX Result
2024-02  - as per formula (above 4 Quarter) - condition fulfill and DAX Result
2024-03 -- as per formula (above 4 Quarter) - condition fulfill and DAX Result
2024-04 -- as per formula (above 4 Quarter) - condition fulfill and DAX Result
2025-01 -- as per formula (above 4 Quarter) - condition fulfill and DAX Result
2025-02 -- 0 - Quarter yet to complete
 
Thanks
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    This is confusing. You wanted to get the average of up to last 4 quarters before current in your previous repsonse but now you want them to return zero. This is also different form your initial example that took the average of the last  4 quarters including the current.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      danextian apologies for confusion, treat as this is final requirement as some internal need changes hence my need get changed slightly DAX Formula, suggest on this.

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        Try this:

        Qtr Avg Running Total 3 = 
// Get latest visible date with data
VAR LatestDateWithData =
    CALCULATE (
        LASTNONBLANK ( Dates[Date], [Total Revenue] ),
        ALLSELECTED ( Dates )
    ) // Get earliest YearQuarter
VAR EarliestQtr =
    CALCULATE ( MIN ( Dates[YearQuarter] ), ALL ( Dates ) )
VAR LatestMonthNumber =
    MONTH ( LatestDateWithData ) // Get month number of latest date
VAR QuarterNumber4Start =
    EDATE (
        CALCULATE ( MINX ( STARTOFQUARTER ( Dates[Date] ), [Date] ), ALL ( Dates ) ),
        3 * 3
    )
VAR LatestYearQuarter =
    FORMAT ( LatestDateWithData, "YYYY0Q" ) // Get YearQuarter key of latest date
VAR RowQuarter =
    MAX ( Dates[YearQuarter] ) // Get YearQuarter key for current row
VAR QuarterEnds = { 3, 6, 9, 12 } // Quarter end months
VAR StartOfQtr =
    MINX ( STARTOFQUARTER ( Dates[Date] ), [Date] )
VAR StartOfPrevQtr = StartOfQtr - 1 // Day before start of current quarter
VAR _Result =
    IF (
        HASONEVALUE ( Dates[YearQuarter] ),
        SWITCH (
            TRUE (),
            StartOfQtr <= QuarterNumber4Start, 0, // Show 0 if earliest quarter
            LatestYearQuarter = RowQuarter
                && NOT LatestMonthNumber
                    IN QuarterEnds && ISFILTERED ( Dates[YearQuarter] ), 0, // Show 0 if latest incomplete quarter
            CALCULATE (
                AVERAGEX (
                    VALUES ( Dates[YearQuarter] ),
                    [Total Revenue]
                ),
                DATESINPERIOD ( Dates[Date], StartOfPrevQtr, -4, QUARTER ),
                REMOVEFILTERS ( Dates )
            )
        )
    )
RETURN
    _Result