Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
here is what i am trying to accomplish:
Target:
Building a Liquidity Report, showing for each business area / company / account the values: Closing Balance, Overdraft, Liquidity, week-over-week change in one single reporting currency
Obstacle:
Balances of accounts are reported for different dates.
Missing exchange rates for reported dates
Solution approach:
For the exchange rates i used the conversion with a calculated column, finding the last available exchange rate, which seems to work fine
For the account balances i tried to use the DAX Pattern - Semi-additive calculations:
+ Finding last available value for each account
+ Finding opening & closing balance
+ Finding Change between two calendar weeks
Problem:
Finding those values for the above mentioned columns worked well, however, on the total level there are values i cannot explain.
Measures:
For example i calculated the overdraft by this measure (the others follow the same logic):
Overdraft Latest =
VAR MaxDate = MAX('Date'[Date])
VAR MaxDates = CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(FactBankBalances, FactBankBalances[IBAN]),
"@MaxDate", CALCULATE(MAX(FactBankBalances[DateKey]))
),
'Date'[Date] <= MaxDate
)
VAR MaxDatesWithLineage =
TREATAS(MaxDates, FactBankBalances[IBAN], 'Date'[Date])
VAR Overdraft = CALCULATE(AVERAGE(FactBankBalances[KK_Linie_Euro]), MaxDatesWithLineage)
VAR Result = CALCULATE(
DIVIDE(Overdraft,
SELECTEDVALUE(ReportNumberFormat[SelectedDivision]),
1)
)
Return
Result
The Data Model:
The matrix report:
With the wrong calculations at the total level.
Thankful for any clue 😉
Bruin87
Solved! Go to Solution.
Hi @Bruin87 ,
Based on your description, maybe you could use ISINSCOPE() or HASONEVALUE() function to define your value of subtotals manually in the matrix visual.
Please refer to the following tutorials.
ISINSCOPE function (DAX) - DAX | Microsoft Docs
HASONEVALUE function (DAX) - DAX | Microsoft Docs
Customizing Hierarchy Measures in DAX using InScope - Visual BI Solutions
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Bruin87 ,
Based on your description, maybe you could use ISINSCOPE() or HASONEVALUE() function to define your value of subtotals manually in the matrix visual.
Please refer to the following tutorials.
ISINSCOPE function (DAX) - DAX | Microsoft Docs
HASONEVALUE function (DAX) - DAX | Microsoft Docs
Customizing Hierarchy Measures in DAX using InScope - Visual BI Solutions
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Bruin87 , based on you columns in rows of matrix try new measure like
sumx(summarize(Table, Table[Column1], Table[Column2], Table[Column2], "_1", [KK]),[_1])
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |