cumulative totals to date by client

[matt_g_tribal]

Hi all, I'm having trouble writing some cumulative totals because I want the formula to work with both variable dates and variable customers. I'm trying to calculate sales to date, where the base formula looks like this:

Balance owed = 
calculate(
sum(FactTransactions[Balance change]),
filter(
ALLSELECTED(FactTransactions),
FactTransactions[month and year] <= max(FactTransactions[month and year]),
)
)

My problem comes because I need to show the results in other locations, where my context is the customer instead of the date. I'm trying to extend the formula, which currently looks like this:

balance owed 2 = 
var maxDate = max(FactTransactions[month and year])
var customer = FactTransactions[customer key]
return 
calculate(
    [Balance change],
    FactTransactions[month and year] <= maxDate && FactTransactions[customer key] = customer,    
    allselected(FactTransactions)
)

This doesn't work becase my variable "Customer" is trying to refer to the current context and the syntax is clearly wrong. I've included it just to demonstrate what I'm trying to do, which is allow all historic dates but only a single client IF the context is single client

[v-deddai1-msft · Accepted solution]

Hi @matt_g_tribal ，

According to your description, my understanding is that you want to compute the cumulative total by [customer key] and [month and year]   If anything is misunderstood, please tell me.

Would you please try to use the following dax code to create the calculated column:

Balance owed1 =

IF (

    HASONEFILTER ( FactTransactions[customer key] ),

    CALCULATE (

        SUM ( FactTransactions[Balance change] ),

        FILTER (

            ALLSELECTED ( FactTransactions ),

            FactTransactions[customer key] <= MAX ( FactTransactions[customer key] )

        )

    ),

    CALCULATE (

        SUM ( FactTransactions[Balance change] ),

        FILTER (

            ALLSELECTED ( FactTransactions ),

            FactTransactions[month and year] <= MAX ( FactTransactions[month and year] )

        )

    )

)

Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ESIVHFYFMiBBm_WhRz...

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

Best Regards,

Dedmon Dai

[amitchandak]

@matt_g_tribal , Under quick formula you have an option for running total. That will give you the required measure.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490