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, I have an issue I'm trying to work through.
I have a list which is updated daily of bank balances, with numerous bank accounts. However, some bank accountants don't necessarily have a new bank balance all 5 working days but we need those missing days.
Also, after completing the list, I need a total balance of all bank balances per day.
Here is how the data currently looks including what I need (except total balance of all bank balances per day):
I's like to see a suggestion for how this can be done.
Thank you for any help you can provide!
Solved! Go to Solution.
Hi @EdJ ,
We can create a new table and two new columns to meet your requirement.
1. Create a new table that contains whole date and name.
Result table =
CROSSJOIN(
VALUES('Table'[Name]),
VALUES('Table'[Date]))
2. Then we need to add the value column.
Value =
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]='Result table'[Date] && 'Table'[Name]= 'Result table'[Name]))
3. At last we can create the result column.
Column =
VAR _date =
IF (
ISBLANK ( 'Result table'[Value] ),
MAXX (
FILTER (
'Result table',
'Result table'[Date] < EARLIER ( 'Result table'[Date] )
&& 'Result table'[Value] <> BLANK ()
&& 'Result table'[Name] = EARLIER ( 'Result table'[Name] )
),
'Result table'[Date]
),
'Result table'[Date]
)
RETURN
IF (
ISBLANK ( 'Result table'[Value] ),
CALCULATE (
SUM ( 'Result table'[Value] ),
FILTER (
'Result table',
'Result table'[Date] = _date
&& 'Result table'[Name] = EARLIER ( 'Result table'[Name] )
)
),
CALCULATE ( SUM ( 'Result table'[Value] ) )
)
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @EdJ ,
We can create a new table and two new columns to meet your requirement.
1. Create a new table that contains whole date and name.
Result table =
CROSSJOIN(
VALUES('Table'[Name]),
VALUES('Table'[Date]))
2. Then we need to add the value column.
Value =
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]='Result table'[Date] && 'Table'[Name]= 'Result table'[Name]))
3. At last we can create the result column.
Column =
VAR _date =
IF (
ISBLANK ( 'Result table'[Value] ),
MAXX (
FILTER (
'Result table',
'Result table'[Date] < EARLIER ( 'Result table'[Date] )
&& 'Result table'[Value] <> BLANK ()
&& 'Result table'[Name] = EARLIER ( 'Result table'[Name] )
),
'Result table'[Date]
),
'Result table'[Date]
)
RETURN
IF (
ISBLANK ( 'Result table'[Value] ),
CALCULATE (
SUM ( 'Result table'[Value] ),
FILTER (
'Result table',
'Result table'[Date] = _date
&& 'Result table'[Name] = EARLIER ( 'Result table'[Name] )
)
),
CALCULATE ( SUM ( 'Result table'[Value] ) )
)
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @EdJ
This can be best done in Power Query
Can you paste the current table in text-tabular format here, instead of a screen cap, so that the contents can be copied easily?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |