Measure or column to aggregate running total sales figures with missing facts

[amorris78]

I am trying to work out the right DAX functions that can help calculate sales figures. I have a data source that forecasts total monthly sales for a particular Customer and sales campaign, however it is missing the accrual of previous monthly campaign subscription numbers.

In Excel, it would look something like this:

Ideally I would like to support DirectQuery mode, but Import mode is OK. I am trying to wrap my head around tablescans and previous rows.

The column headers and row data look something like:

Customer_Name[text], Campaign_Name[text], Campaign_Stage[text], Stage_Date[date/time], Subscription_change_in_value[fixed_decimal]

Foo, X New Flavour, Local Distribution, 24/11/2022 00:00:00, 550

[amorris78]

Thanks @daXtreme,

I now see that perhaps I may not have made my problem all that clear in my original post. I have the running total by month column. However, my gap in the problem is the Total as PBI displays it. I am wanting that calculation to sumTotal all of the values as my total revenue is not $72,616, but $762,468 as it is a subscription.

As the measure gets summarised, I can't seem to calculate the value I am looking for as it returns $72,616. Do I need to create a new table and then sum those rows to be able to see the value of $762,468

[daXtreme · Accepted solution]

Hi

All you need to do is to detect whether there's only 1 month visible (perhaps using the function HASONEFILTER) in the context or if there are many. If one, you use your measure as is. If many, you change the measue so that the sum for all months is obtained. You'll have to use ALLSELECTED in the second case.

Of course, you'll have to use IF in the new measure to distinguish between the two cases.

[daXtreme]

@amorris78 

All you need is this: Computing running totals in DAX (sqlbi.com)

[amorris78]

@daXtreme That assumes that there is a value in each month of the sales data. The example you linked for RT only aggregates fields with values. As mentioned in the problem statement, I am looking to calculate the accrual of subscription values - ie the blank values in the still need to be accrued as the data only shows delta in subscription changes. refer to Excel example.

[daXtreme]

Hi @amorris78 

It does not matter whether there is or not any data for any month. The rule is always the same. Your picture above shows clearly that you've got a classical running total (column C), so please follow the guide.