topic Re: Calculate how many months under target in a row in Power BI in DAX Commands and Tips

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

ARodr3 — Wed, 12 Feb 2025 21:36:07 GMT

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

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

Anonymous — Thu, 13 Feb 2025 03:20:17 GMT

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

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

Jihwan_Kim — Thu, 13 Feb 2025 04:38:34 GMT

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] ) )