Calculate how many months under target in a row in Power BI

[ARodr3]

Hi guys!. I´m brand new with Power BI. I´m trying to figure out how to create a measure that calculates how many months in a row a client has not meet with a target using DAX measures in a visual table, and restart when this client reachs the target. I let you an example of what I'm looking for: 

As you can see, I have a column which indicates the month of the year. I have a measure that indicates the difference between sales and target sales, a measure with 1 if this difference is negative and 0 if is positive. Finally, i´m looking for a measure that bring me a running sum of how many months in a row is under target, and restart the sum when the client gets the target of sales. 

Have any idea of how can i get this?

Thanks 

[Anonymous · Accepted solution]

Hi, @ARodr3 
You can use new visual calculation function to achieve your need.

Running sum = IF([Out of Target?]<>0,RUNNINGSUM([Out of Target?]),0)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I tried to use the WINDOW DAX function in the measure.

WINDOW function (DAX) - DAX | Microsoft Learn

diff target vs actual: = 
SUM( sales_fct[target_sales] ) - SUM( sales_fct[actual_sales] )

WINDOW function (DAX) - DAX | Microsoft Learn

Running months below target: = 
VAR _current =
    MAX ( calendar_dim[Year-Month sort] )
VAR _condition =
    ADDCOLUMNS (
        SUMMARIZE (
            ALL ( sales_fct ),
            calendar_dim[Year-Month],
            calendar_dim[Year-Month sort]
        ),
        "@diff", [diff target vs actual:],
        "@condition",
            INT ( [diff target vs actual:] < 0 ) + 0
    )
VAR _group =
    ADDCOLUMNS (
        _condition,
        "@group",
            INT (
                MAXX (
                    OFFSET ( -1, _condition, ORDERBY ( calendar_dim[Year-Month sort], ASC ) ),
                    [@condition]
                ) = 0
                    || [@condition] = 0
            )
    )
VAR _partition =
    ADDCOLUMNS (
        _group,
        "@partition",
            SUMX (
                WINDOW (
                    1,
                    ABS,
                    0,
                    REL,
                    _group,
                    ORDERBY ( calendar_dim[Year-Month sort], ASC )
                ),
                [@group]
            )
    )
VAR _running =
    ADDCOLUMNS (
        _partition,
        "@running",
            SUMX (
                FILTER (
                    _partition,
                    [@partition] = EARLIER ( [@partition] )
                        && calendar_dim[Year-Month sort] <= EARLIER ( calendar_dim[Year-Month sort] )
                ),
                [@condition]
            )
    )
RETURN
    IF (
        HASONEVALUE ( calendar_dim[Year-Month] ),
        MAXX (
            FILTER ( _running, calendar_dim[Year-Month sort] = _current ),
            [@running]
        )
    )

[Anonymous · Accepted solution]

Hi, @ARodr3 
You can use new visual calculation function to achieve your need.

Running sum = IF([Out of Target?]<>0,RUNNINGSUM([Out of Target?]),0)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum