Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filtered Running Total

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.

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

     

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      This solution worked, thank you Icey!