How do I calculate the sum of Value for the last 6 sprints using DAX

Question

Problem:
I need a calculated measure in DAX that sums the Value column for the last 6 sprints. I am basing the last 6 sprints on the DimSprintEndDateKey in descending order.

Table structure in PowerBI

The DAX that I am using:

CALCULATE (
    SUM ( factSprint[Value] ),
    FILTER (
        ALL ( factSprint ),
        COUNTROWS (

        topn(6,

            FILTER (
                factSprint,
                EARLIEST( RELATED ( dimSprint[DimSprintEndDateKey] ) )
                    > RELATED ( dimSprint[DimSprintEndDateKey] )
            ),RELATED ( dimSprint[DimSprintEndDateKey] ), DESC


            )

        )
    )
)

Out of curiosity, why do you have both a fact and dimension table for your sprints? — Rory
The dimension data is coming from VSTS and the fact data is coming from multiple sources. The client is providing the data and has not yet given us a way to tie the sources together. — Brian Erlich

Marco Vos Marco Vos · Accepted Answer · 2018-11-06T09:44:19

I am assuming that the relationship on your tables is between 'dimSprint'[dimSprintKey] and 'FactSprint'[dimSprintKey].

That being the case, this measure could work for you.

Total Value Last Six Sprints =
VAR endDateSprint =
    LOOKUPVALUE (
        'dimSprint'[dimSprintEndDateKey],
        'dimSprint'[dimSprintKey], SELECTEDVALUE ( 'FactSprint'[dimSprintKey] )
    )
VAR dimTableFiltered =
    FILTER ( 'dimSprint', 'dimSprint'[dimSprintEndDateKey] <= endDateSprint )
RETURN
    CALCULATE (
        SUM ( 'FactSprint'[Value] ),
        ALL ( 'FactSprint' ),
        TOPN ( 6, dimTableFiltered, [dimSprintEndDateKey], DESC )
    )

Use it on a matrix visual (or pivottable). Be sure to put 'FactSprint'[dimSprintKey] or 'FactSprint'[SprintPK] on Rows and [Total Value Last Six Sprints] on Values.

How do I calculate the sum of Value for the last 6 sprints using DAX

1 Answers