Running Total With Logic

[pbianalyst75354]

Hey everyone,

I have included the link to my Dropbox containing two files: Test.xlsx and Questions.pbix. Link is here. The result I am going for is mirroring the Excel formula found in column L (Calc'd Count column) of the Excel file "Test.xlsx" in a calculated column. The result should produce this:

Currently, I am having trouble getting the DAX code to produce the number of locationsPerInterval (2) for each month and computing the remaining amount when the locations have been met. A more indepth explanation is below.
Example:

If my locations = 13 (total locations),
Delay is 24 (Starting Date),
an interval of 1 (once a month),

and location/interval of 2 (locations available during each interval)

..then I should start seeing 2 locations become available 24 months from the first date of my dataset (09/30/2024) and each proceeding date (10/31/2024, 11/30/2024, and so on) should result to 2 until the number of locations (13) has been met. So in this example, I should see a number 2 for months 08/31/2026 though 01/31/2027 and a 1 in 02/28/2028. The 1 is produced for 02/28/2028 because the sum of the prior months equal 12 (2, 2, 2, 2, 2, 2) and there is one remaining location for Feb. And since my total locations of 13 has been met the remaining months after 02/28/2028 should result to 0. 

I hope this is clear. Thanks so much in advance if you look at this!

[v-tangjie-msft · Accepted solution]

Hi @pbianalyst75354 ,

You can create a column.

Column = var _delaystart=RELATED(InputVariables[Dev Delay])
var _inter=RELATED(InputVariables[Loc/Interval])
var _loc=RELATED(InputVariables[Locations])
var _divide=ROUNDDOWN(DIVIDE(_loc,_inter),0)
var _minend=_divide+_delaystart-1
var _maxend=_divide+_delaystart
var _mod=MOD(_loc,_inter)
return IF([Index]<_delaystart,0,IF([Index]>=_delaystart&&[Index]<=_minend,_inter,if([Index]=_maxend,_mod,0)))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[lbendlin]

Based on our provided sample data. indicate the complete expected outome.

The result I am going for is mirroring the Excel formula found in column L (Calc'd Count column) of the Excel file "Test.xlsx" in a calculated column.

Can you describe the formula?  Don't assume that we all have Excel installed.

[pbianalyst75354]

Thanks for replying @lbendlin. The Excel formula is doing what I described in my example explanation: "produce the number of locationsPerInterval (2) for each month and computing the remaining amount when the locations have been met."