Filtered Running Total

[Anonymous]

Trying to create a prior 7 years Total in the Install Base row, so first year that should be displayed in this row would be 2015, and it would pull from Total Sold row for years 2008-2014.  Looking at image below, you can see in the Install Base row I am getting a total of 225,291, where I should really be getting 1,005,727 (Sum of years from 2008-2014 from Total Sold).  

Greatly appreciate any help on correcting my formula.

[Icey · Accepted solution]

Hi @Anonymous ,

You can try this:

previous 7 years total =
CALCULATE (
    [Total Sold],
    DATESINPERIOD (
        'Whole Goods'[Fiscal Date],
        LASTDATE ( 'Whole Goods'[Fiscal Date] ),
        -7,
        YEAR
    )
)

Install Base =
VAR mindate =
    CALCULATE (
        MIN ( 'Whole Goods'[Fiscal Date] ),
        ALLSELECTED ( 'Whole Goods'[Fiscal Date] )
    )
RETURN
    IF (
        YEAR ( MAX ( 'Whole Goods'[Fiscal Date] ) ) - 7
            < CALCULATE (
                YEAR ( FIRSTDATE ( 'Whole Goods'[Fiscal Date] ) ),
                ALL ( 'Whole Goods' )
            ),
        BLANK (),
        CALCULATE (
            [previous 7 years total],
            FILTER (
                ALL ( 'Whole Goods' ),
                DATEADD ( 'Whole Goods'[Fiscal Date], 1, YEAR )
                    <= MAX ( 'Whole Goods'[Fiscal Date] )
                    && DATEADD ( 'Whole Goods'[Fiscal Date], 1, YEAR ) >= mindate
            )
        )
    )

This is my PBIX file.

Best Regards,

Icey

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

[Anonymous]

This solution worked, thank you Icey!

[parry2k]

@Anonymous as best practice, it is good to have date dimension in your model and then run time based calculation from date dimension. There are many posts on how to add date to your data model. Once you have date dimension, you can use DATESBETWEEN function to achieve the goal.

[Anonymous]

Thank you for the advice Perry 2k but I was still unable to get this to come back with the data I was looking for.  I am still new to Power Bi so I am sure it is user error, but the suggestion that Icey  supplied was able to get me the data that I needed.  Thank you again for your input.