Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Rolling Year Measure with a Filter

Jump to Best Answer
  • 1.  Rolling Year Measure with a Filter

    Posted 13 days ago
    I currently am using the measure below to calculate a rolling year for units sold. It has been working great and I haven't had any issues with it calculating properly. Now, I'm not the most familiar with DAX and I want to add a condition to this measure to limit it just the type of units sold (new or refurbished). Would someone be able to please help me with the correct framework in which to accomplish this?

    TIA!

    Rolling Total Units =
    var _lastvisibledate = LASTDATE('Calendar'[Date])
    var _canmove12mback =
    NOT ISBLANK( DATEADD(_lastvisibledate,-1,YEAR) )
    var _result =
    CALCULATE(
    SUM('NEMA Compare Data'[Unit]),
    DATESINPERIOD(
    'Calendar'[Date],
    _lastvisibledate,
    -1,
    YEAR
    )
    )
    return
    if(_canmove12mback,_result)


    ------------------------------
    Aimee Harris
    Delta Medical Systems
    Brookfield WI
    ------------------------------


  • 2.  RE: Rolling Year Measure with a Filter
    Best Answer

    Top Contributor
    Posted 11 days ago
    Hi Aimee,
    Just add another argument to filter on the type. assuming that the type field is in the 'MENA compare data' table I invite you to test this measure

    Rolling Total Units =
    VAR _lastvisibledate =
        LASTDATE ( 'Calendar'[Date] )
    VAR _canmove12mback =
        NOT ISBLANK ( DATEADD ( _lastvisibledate, -1, YEAR ) )
    VAR _result =
        CALCULATE (
            SUM ( 'NEMA Compare Data'[Unit] ),
            DATESINPERIOD ( 'Calendar'[Date], _lastvisibledate, -1, YEAR ),
            'NEMA Compare Data'[Unit]="new" || 'NEMA Compare Data'[Unit]="refurbished"
        )
    RETURN
        IF ( _canmove12mback, _result )
    ​
    This is a article from @Alberto Ferrari about the subject : Specifying multiple filter conditions in CALCULATE - SQLBI

    If this help, mark the reply as "Recommend"​

    ------------------------------
    Best regards
    Mehdi HAMMADI
    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 3.  RE: Rolling Year Measure with a Filter

    Posted 10 days ago
    This worked perfectly! Thank you so much for your help!

    ------------------------------
    Aimee Harris
    Delta Medical Systems
    Brookfield WI
    ------------------------------