Forum Discussion

MichelleOmondi's avatar
MichelleOmondi
Regular Visitor
3 years ago

Cumulative Totals with a SWITCH statement

I'm trying to calculate cumulative totals per country.

Here is the DAX code I'm using to calculate Currencies:

Outstanding Currencies =

VAR Debit = SUM('model FactGLTransactions'[Debit])
VAR Credit = SUM('model FactGLTransactions'[Credit])
VAR Outstanding = Debit - Credit

VAR SelectedCountry =
SELECTEDVALUE('model FactGLAccounts'[Country])

RETURN
SWITCH(
    TRUE(),
    SelectedCountry = "South Korea", "KRW" & " " & FORMAT(Outstanding, "Standard"),
    SelectedCountry = "China", "CNY" & " " & FORMAT(Outstanding, "Standard"),
    Outstanding
)
 
Then I feed it into the DAX calculating Cumulative Totals
BS Cumulative Totals Currencies =

-- Contains Cumulative Totals per Currency Sign

VAR MaxDate = MAX ('Calendar'[Date] )

VAR CalendarMax =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALL ( 'Calendar' ) )

RETURN
    IF (
        MaxDate <= CalendarMax,
        CALCULATE (
            [Outstanding Currencies],
            FILTER ( ALL ('Calendar' ), 'Calendar'[Date] <= MaxDate)
        )
    )
The above code works well.
The problem arises when I need to convert the currencies to USD. In the month there were no transactions, I'm getting blanks
The code I'm using is:

Test Cumulative Totals Currencies =

VAR MaxDate = MAX ('Calendar'[Date] )

VAR CalendarMax =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALL ( 'Calendar' ) )

VAR RateTest = 100

RETURN
SWITCH(
    SELECTEDVALUE('model FactGLAccounts'[Country]),
    "South Korea",
    IF (
        MaxDate <= CalendarMax,
        CALCULATE (
            [Outstanding]/ RateTest,
            FILTER ( ALL ('Calendar' ), 'Calendar'[Date] <= MaxDate)
        )
    ),
    "China",
    IF (
        MaxDate <= CalendarMax,
        CALCULATE (
            [Outstanding]/ RateTest,
            FILTER ( ALL ('Calendar' ), 'Calendar'[Date] <= MaxDate)
        )
    ),
    [Outstanding]/ RateTest
)
 
I've realized the Switch or IF equivalent, throws off the cumulative totals calculation and it doesn't pick the previous value if the current month has no transactions. Instead it leaves it as blank.
Please help

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MichelleOmondi ,

     

    Your calculation is based on your data model, please share a sample file with me. And you can show me a screenshot with the result you want. This will make it easier for me to find the solution.

     

    Best Regards,
    Rico Zhou

     

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