Cummulative per period frequency

Anonymous , Create a date table, with half year, month, qtr. and then create a measure like

 

sum(Table[Amount])+0 and then try to display this

 

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.

Hi Anonymous ,

 

Please follow these steps:

 

1. Add a Month-Day column:

Month-Day = FORMAT([Month Start],"MMM-DD")

2.Create a Calendar table:

Calendar =
CROSSJOIN (
    DISTINCT (
        SELECTCOLUMNS (
            CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
            "Year", YEAR ( [Date] ),
            "QuarterName", "Q" & FORMAT ( [Date], "Q" ),
            "Month", MONTH ( [Date] ),
            "Month-Day", FORMAT ( [Date], "MMM-21" )
        )
    ),
    VALUES ( 'Table'[Employee] )
)

3. Add columns to Calendar table:

Amount Column = LOOKUPVALUE('Table'[Amount],[Employee],[Employee],'Table'[Month-Day],[Month-Day])

Frequency Column = LOOKUPVALUE('Table'[Frequency],[Employee],[Employee])

4. Now try the following formula to create measure:

Measure =
VAR _value =
    CALCULATE (
        SUM ( 'Calendar'[Amount Column] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[Employee] )
    )
VAR _minMonth =
    CALCULATE (
        MIN ( 'Calendar'[Month] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Frequency Column] = MAX ( 'Calendar'[Frequency Column] )
                && FIRSTNONBLANK ( 'Calendar'[Amount Column], TRUE () )
        )
    )
RETURN
    SWITCH (
        MAX ( 'Calendar'[Frequency Column] ),
        "Quarterly",
            IF (
                MAX ( 'Calendar'[Month] ) >= _minMonth
                    && MOD ( MAX ( 'Calendar'[Month] ) - _minMonth, 3 ) = 0,
                _value,
                BLANK ()
            ),
        "Annual", FIRSTNONBLANK ( 'Calendar'[Amount Column], TRUE () ),
        "Monthly", IF ( MAX ( 'Calendar'[Month] ) >= _minMonth, _value, BLANK () ),
        "BiAnnual",
            IF (
                MAX ( 'Calendar'[Month] ) = MAXX ( ALL ( 'Calendar' ), [Month] )
                    || MAX ( 'Calendar'[Month] ) = _minMonth,
                _value,
                BLANK ()
            )
    )

5. Use ISINSCOPE() to get cummulative total in Matrix:

Cummulative =
VAR _value =
    IF (
        [Measure] = BLANK (),
        BLANK (),
        SUMX (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
                    && 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
            ),
            [Measure]
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Calendar'[Month-Day] ),
        _value,
        IF (
            ISINSCOPE ( 'Calendar'[QuarterName] ),
            BLANK (),
            SUMX ( 'Calendar', [Measure] )
        )
    )

The final output is shown below:

 

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