I have column called MIS (month in Service) . It can have different values 0,2,3,4 etc. I have another custom column that calculate the maximum MIS that particular record should have. All I want to do is that start the MIS from 0 to max MIS and I am not sure how to do.
The column “Number” is the maximum number that MIS should go to. “Count without zero” is simple count. And running total is just that. The idea bahind is that MIS should go to 18 (Number) and just repeating the running total.
Any help will be greatly appreciated.
It’s hard to see exactly what you are referring to here, but if you are looking to loop through and find the max, you would want to use MAXX. the “X” functions are basically loops.
Hi Nick,
My apologies for not being clear. I want MIS column to go to the value of Number column. so the output should be something like
201801 18 0 1 1
201801 18 1 0 1
201801 18 2 0 1
201801 18 3 1 2
201801 18 4 1 3
201801 18 5 0 3
201801 18 6 2 5
201801 18 7 1 6
201801 18 8 0 6
All the way to
201801 18 18 0 6
Hopefully that will help understand what I am trying to do.
Have you tried using the MIN function here.
Something like this.
MIN( MIScolumn, Numbercolumn )
This would give you the min amount up to the 18 number you mention, then it would remain 18 from then on.
Other than this I’m a bit confused overall by the requirement.
Maybe share a pbix file so that this can be look at further and all variables can be known.
Thanks
Sam