DAX Formula Problem with running totals and if statments

[btodres]

I am tracking the number of ads served by day and cumulative for the year. There is a cap on how many ads can be served over the life time and in many instances we serve ads in excess of the capped amount. For billing purposes we can only bill up to the caped amount. I need to create a dax formula that looks at the number of ads served today and determines if all of them are billable, none are billable or if only a portion of them are billable. The last column in the below chart is the result I need. I tried various versions of if statements in DAX to no avail. please HELP! thx!!

[TABLE="width: 433"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Ads Served
[/TD]
[TD]Running Total
[/TD]
[TD]Max Ads Permitted Lifetime
[/TD]
[TD]Billable Ads for Day
[/TD]
[/TR]
[TR]
[TD]4/29/2013
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]662,845
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]4/30/2013
[/TD]
[TD]5,112
[/TD]
[TD]5,121
[/TD]
[TD]662,845
[/TD]
[TD]5,112
[/TD]
[/TR]
[TR]
[TD]5/1/2013
[/TD]
[TD]156,158
[/TD]
[TD]161,279
[/TD]
[TD]662,845
[/TD]
[TD]156,158
[/TD]
[/TR]
[TR]
[TD]5/2/2013
[/TD]
[TD]503,683
[/TD]
[TD]664,962
[/TD]
[TD]662,845
[/TD]
[TD]501,566
[/TD]
[/TR]
[TR]
[TD]5/3/2013
[/TD]
[TD]209,171
[/TD]
[TD]874,133
[/TD]
[TD]662,845
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jacob Barnett]

Assuming your table is called 'Fact' you should build it up using some intermediate measures:

[Ads] = sum(Fact[Ads Served])

[Ads Running Total] = SUM(Fact[Running Total])

[Max Ads] = CALCULATE(MAX(Fact[Max Ads Permitted Lifetime]),ALL(Fact[Date]))

[Daily Billable] = IF([Ads Running Total]<=[Max Ads],            
                          [Ads], 
                          IF([Ads]+[Max Ads]-[Ads Running Total]<0,0,[Ads]+[Max Ads]-[Ads Running Total]
                             )
                           )

The second half of the original IF isn't particularly elegant but unfortunately MIN/MAX work purely on columns in DAX unlike in Excel.

The [Max Ads] measure may need adjustment if your real data is more complex e.g. with Campaign/Client also in there.

 

[btodres]

Assuming your table is called 'Fact' you should build it up using some intermediate measures:

[Ads] = sum(Fact[Ads Served])

[Ads Running Total] = SUM(Fact[Running Total])

[Max Ads] = CALCULATE(MAX(Fact[Max Ads Permitted Lifetime]),ALL(Fact[Date]))

[Daily Billable] = IF([Ads Running Total]<=[Max Ads],            
                          [Ads], 
                          IF([Ads]+[Max Ads]-[Ads Running Total]<0,0,[Ads]+[Max Ads]-[Ads Running Total]
                             )
                           )

The second half of the original IF isn't particularly elegant but unfortunately MIN/MAX work purely on columns in DAX unlike in Excel.

The [Max Ads] measure may need adjustment if your real data is more complex e.g. with Campaign/Client also in there.

thanks for your reply - appreciate it. Unfortunately it does not work and i do have the added complexity that you mention at the end of your post regarding campaign specific items in the report. I have really only grasped very basic levels of DAX which gets me to about 90% of what I need but its the extra 10% that is killing me. I can usually figure out just about any complex formula needed in excel but DAX is another story altogether and I have no formal database training it has been all learn as you go. I think I may need to do some DAX training to get to the next level - struggling way to much on some of these things.