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
