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
Hello.
I have tried several things and maybe I am just thinking too complicated. Therefore I will ask you for support 😉
In general I have already a workaround in Excel, as I started long ago with a dashboard in Excel. But now I want to transfer it. I have the following situation:
In my table there are listed several accounts with several bookings on the same days, but not on each day in a month (like a normal bank account).
Date | Account | Booking | End of day Value |
25.01.2021 | A | 150 | 150 |
28.01.2021 | B | 5000 | 5000 |
28.01.2021 | A | -50 | 100 |
27.02.2021 | A | -60 | 40 |
28.02.2021 | B | -500 | 3000 |
28.02.2021 | B | -1500 | 3000 |
15.03.2021 | A | 360 | 400 |
21.03.2021 | B | 3200 | 6200 |
... | |||
01.04.2021 | A | -30 | 370 |
Now I would like to extract the "End of day value" for each account per month & year. But I cannot take the last day of each month like 31.01.2021, 28.02.2021 ... as it is not a must that there was a booking on those days.
Date | Account | End of day value |
28.01.2021 | A | 100 |
28.01.2021 | B | 5000 |
27.02.2021 | A | 40 |
28.02.2021 | B | 3000 |
15.03.2021 | A | 400 |
21.03.2021 | B | 6200 |
.... |
I don't care if it's just a visual + filtering or if I create a DAX statement for a new table or measure.
Here my comparison of what I have (many data points)
and what I want to achieve (just one data point for each month+year)
I hope you got my point. It's a little bit difficult to describe 😉
Hi @MoFiMo ,
You can try to create a max date of monthly column:
Max_date = CALCULATE(MAX('Table'[Date]),FILTER('Table',EARLIER('Table'[Account])='Table'[Account]&&EARLIER('Table'[Date].[Year])='Table'[Date].[Year]&&EARLIER('Table'[Date].[MonthNo])='Table'[Date].[MonthNo]))
Then create a new table:
Table 2 =
distinct(SELECTCOLUMNS(FILTER('Table','Table'[Date]='Table'[Max_date]),"account",'Table'[Account],"date",'Table'[Date],"end_of_value",'Table'[End of day Value]))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello:
You can use this measure, I believe:
Sorry [Value Amt] is just the sum of the ending balances.
Thanks for your replies. It seems that it is more complicated than I thought.
As I haven't done so much with DAX until now, I will need to start from the beginning and then I will try your suggestions 😉
But I am still open for other proposals ... there are always many different ways for problems like that 😄
You can create a calculated table like that by adding a month column to group by and then finding the maximal date for each account/month combination.
For example,
Table2 =
VAR AddMonth = ADDCOLUMNS ( Table1, "MonthEnd", EOMONTH ( Table1[Date], 0 ) )
VAR FilterDate =
FILTER (
AddMonth,
Table1[Date]
= MAXX (
FILTER (
AddMonth,
Table1[Account] = EARLIER ( Table1[Account] )
&& [MonthEnd] = EARLIER ( [MonthEnd] )
),
Table1[Date]
)
)
RETURN
SELECTCOLUMNS (
FilterDate,
"Date", Table1[Date],
"End of day Value", Table1[End of day Value]
)
I think it might be better as a measure though like this:
LastEoDValue =
VAR CurrDay = MAX ( Table1[Date] )
VAR LastEoD =
CALCULATE (
MAX ( Table1[Date] ),
FILTER (
ALLSELECTED ( Table1[Date] ),
EOMONTH ( Table1[Date], 0 ) = EOMONTH ( CurrDay, 0 )
)
)
RETURN
CALCULATE (
SUM ( Table1[End of day Value] ),
Table1[Date] = LastEoD
)
I think you need some DAX here and to make use of LASTNONBLANK.
Have a read of https://www.sqlbi.com/articles/optimizing-lastnonblank-and-lastnonblankvalue-calculations/
Do you already have a date table in your model?
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 | |
15 | |
14 | |
13 | |
9 |
User | Count |
---|---|
60 | |
23 | |
22 | |
19 | |
13 |