Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a requirement where I have to do the currency conversion for my measure on row level and add them up to get correct data on top levels . Data in my 'Balance Sheet Measures', is available for only July month of each year and because of this I can see the Numbers in July Month only in matrix. Is there any way I can see the July amount in each month , even though it doesn't exist. Basically I want July amount in each Month of that year. I have done summarize here and then Sumx so that different conversion happen on row level and I want to see the sum of those on Month Level.
This is the DAX I have used
VAR BSBalance =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE (
'Balance Sheet Measures',
'Date - Report'[DETL_ReportDateSurrogateKey],
'Currency - Local'[Currency Code]
),
"CBSBalanceAmount", [Balance Sheet Actual Amount] ,
"CRate", CALCULATE ( AVERAGE ( 'Currency - Exchange Rate'[Currency Rate Amount] ) )),
KEEPFILTERS ( TREATAS ( { "M" }, 'Currency - Exchange Rate'[Rate Type Code] ) )
)
VAR BSResult =
SUMX (
BSBalance ,
IF (
NOT ISBLANK ( [CRate] ) ,
[CBSBalanceAmount] / [CRate],
BLANK ()
)
)
RETURN
BSResult
Solved! Go to Solution.
I am assuming you have a relationship between 'Date - Report'[DETL_ReportDateSurrogateKey] and a date column in your currency table.
One way to solve this would be to create a "Day of Month" column in each, and use this as your relationship and inside the summarize. This would make it agnostic of the Month.
If you have multiple years, you would just need to add this into the column you create. It would result in a many to many join, so would need to be tested. But it's the simplest route without getting into over complex DAX.
I am assuming you have a relationship between 'Date - Report'[DETL_ReportDateSurrogateKey] and a date column in your currency table.
One way to solve this would be to create a "Day of Month" column in each, and use this as your relationship and inside the summarize. This would make it agnostic of the Month.
If you have multiple years, you would just need to add this into the column you create. It would result in a many to many join, so would need to be tested. But it's the simplest route without getting into over complex DAX.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |