Obtain Last End of Month Value

[Anonymous]

I have attached mock data for better explanation. My data is reported daily, multiple times a day, with no consistency of the time of day. I want to create a table showing the last value reported for each month (indicated in the image by highlighted values). This would be the cell highlighted above, NOT a sum of the month NOR sum of the day. Does anyone know a way to do this?

[rsbin]

@Anonymous ,

First create a Calculated Column to obtain year-month:

Year-Month = FORMAT( [Date], "yyyy-mm" )

I am assuming your data will eventually cross years.  If not, then perhaps you just need a Month column.

Then I created a Summary Table:

LastValuesTable5 = SUMMARIZECOLUMNS( Table5[Year-Month],
                    "LastDate", MAX( Table5[Date] ),
                     "LastValue", CALCULATE( MAX( Table5[Amount] ), 
                                     FILTER( Table5, Table5[Date] = MAX( Table5[Date] ))))

Replace 'Table5' with the name of your table. I believe this is the result you are looking for:

Year-Month           LastDate              LastValue

2022-01	1/31/2022 12:01:59 AM	2.46
2022-02	2/28/2022 12:24:00 PM	9.54

Regards,

[Anonymous]

For the creation of a summary table, where would i put that formula in? In Power Query or in the main power bi page?

[rsbin]

@Anonymous ,

In Power BI

Select New Table.

[Anonymous]

Thank you. I found another obstacle. Some of the dates & times are exactly the same. How can I adjust the formula to take the very last day & time input even when the days and times are the same? I can draft mock data if that's easier too

[rsbin]

@Anonymous ,

Don't quite understand the obstacle.  If the last date and time are the exact same for more than one record, what are you using to decide which record to keep?

Please post mock data, but post it as a table, not as a picture.  Keep this in mind for all future posts.

[Anonymous]

Type	Date	amount	id
a	1/1/2022  21:11:22 PM	1	1
a	1/5/2022  21:59:22 PM	2	2
b	1/11/2022  22:01:22 PM	1.1	3
b	1/19/2022  22:01:22 PM	2.4	3
a	1/31/2022  23:01:22 PM	1.5	4
a	1/31/2022  23:01:22 PM	1.9	5
a	2/1/2022  21:11:22 PM	2.5	6
a	2/5/2022  21:59:22 PM	2.8	7
b	2/11/2022  22:01:22 PM	6.4	8
b	2/19/2022  22:01:22 PM	4.3	9
a	2/31/2022  23:01:22 PM	8.5	10
a	2/31/2022  23:01:22 PM	3.7	11

Here is a new mock table. I have 2 different item types (a & b). There are some dates&times that are exact and only distinguishable by ID column. This way, I can create a power bi table with the last recorded amounts for each item for each month. the data results are as shown below: (Thank you again!!)

Type	Date	amount	id
b	1/19/2022  22:01:22 PM	2.4	3
a	1/31/2022  23:01:22 PM	1.9	5
b	2/19/2022  22:01:22 PM	4.3	9
a	2/31/2022  23:01:22 PM	3.7	11

[rsbin · Accepted solution]

@Anonymous ,

Please modify the new Table as this:

LastValuesTable5a = SUMMARIZECOLUMNS( Table5a[Year-Month], Table5a[Type],
                            "LastDate", CALCULATE( MAX( Table5a[Date] ),
                                         FILTER(Table5a, Table5a[id] = MAX(Table5a[id]))),
                            "LastValue", CALCULATE( MAX( Table5a[Amount] ), 
                                         FILTER( Table5a, Table5a[id] = MAX( Table5a[id] ))))

LastDateLastValueYear-MonthType

1/19/2022 10:01:22 PM	2.4	2022-01	b
1/31/2022 11:01:22 PM	1.9	2022-01	a
2/19/2022 10:01:22 PM	4.3	2022-02	b
2/28/2022 11:01:22 PM	3.7	2022-02	a

Also, Feb only has 28 days, not 31.  Ensure your DateTime column is formatted as such and not Text.

Regards,

[Anonymous]

Oops. The Feb 31st was due to me typing in mock data. Thank you so much! This worked. I really appreciate your assistance.