Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team - I would like to know any suggestions you have to get a specified balance sheet totals for specified date (especially looking at end of financial year "30-06-2019")? I've got certain matrix visual with columns for balance sheet total where you can have a slicer for FY and Monthly selection. However, I can't seem to get the right number for balance sheet for end of financial year when i select FY20 and another selection for month.
Here's the formula I used:
Solved! Go to Solution.
Hello,
For prior financial year end balance, you may try hardcoding the actual date such as below:
CALCULATE (
SUM ( Fact[Amount] ),
FILTER ( ALL ( DatesTable ), DatesTable[Date] <= DATE(2019, 1, 1 ) )
)
Or to calculate the running balance before the current financial year in the current filter context, use
CALCULATE (
SUM ( Fact[Amount] ),
FILTER ( ALL ( DatesTable ), DatesTable[FinancialYear] < MIN( DatesTable[FinancialYear] ) )
)
Proud to be a Super User!
Hi @robderay ,
The Balance Sheet balance the running total of a real account at a given day. Try these formulas:
Balance =
//use if you have separate dates table
CALCULATE (
SUM ( Fact[Amount] ),
FILTER ( ALL ( DatesTable ), DatesTable[Date] <= MAX ( DatesTable[Date] ) )
)
Balance =
//if you don't have a separate dates table
CALCULATE (
SUM ( Fact[Amount] ),
FILTER (
ALL ( Fact[JournalDate] ),
Fact[JournalDate] <= MAX ( Fact[JournalDate] )
)
)
These formulas will give you the running total for a give date or if you use month in a visual, the max date in that month, regardless of whether you are following calendar or fiscal year.
Proud to be a Super User!
hi @danextian -
Thanks. I think that was the formula used for the balance sheet from earliest to latest date selected? Which I already used for current balance sheet formula. What I'm looking at is to get a certain balance sheet totals for end of financial year last year on the matrix as well. This is for me to get the variance of any selected month and the totals for end of FY last year ("30-06-2019" for e.g.).
Hello,
For prior financial year end balance, you may try hardcoding the actual date such as below:
CALCULATE (
SUM ( Fact[Amount] ),
FILTER ( ALL ( DatesTable ), DatesTable[Date] <= DATE(2019, 1, 1 ) )
)
Or to calculate the running balance before the current financial year in the current filter context, use
CALCULATE (
SUM ( Fact[Amount] ),
FILTER ( ALL ( DatesTable ), DatesTable[FinancialYear] < MIN( DatesTable[FinancialYear] ) )
)
Proud to be a Super User!
I would like to know why is it that if I select FY19 the end of financial year value is different now? I use the formula you mention to hardcode but it seems its only working when I used the FY20 and if i select anything from FY19 the data didnt match?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |