Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MichelleOmondi
Regular Visitor

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 1
v-rzhou-msft
Community Support
Community Support

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors