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|
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|
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]))
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can use this measure, I believe:
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.
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.
Do you already have a date table in your model?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.