Month End Values

[M_SBS_6]

Hi, I have a list of data that comes into us everyday, Monday to Friday. What I need to show is the values based on the last date we have each month. 

My table as an example:

Date.          Type.  Name. Product Value

01/12/24.   YT.       Macron. Coat.    50

01/12/24.   DA.       Macron Gillet.     30

01/12/24.   DA.      Macron. T-shirt.  10

01/12/24.   YT.       Nike. Coat.    90

01/12/24.   DA.      Nike.  Gillet.    70

01/12/24.   DA.      Nike. T-shirt.  20

……..

31/12/24.   YT.       Macron. Coat.    55

31/12/24.   DA.       Macron Gillet.     20

31/12/24.   DA.      Macron. T-shirt.  5

31/12/24.   YT.       Nike. Coat.    100

31/12/24.   DA.      Nike.  Gillet.    80

31/12/24.   DA.      Nike. T-shirt.  20

So my measure would return the values for Macron £80 for December and £210 for Nike as they’re the latest values for month end. 

The problem is, the months all have a different end date so not too sure what to do but if I want to track the month end position on a line chart to show the trend, I’m not sure how to do it. Anyone know please? 

[Anonymous · Accepted solution]

Hi @M_SBS_6 

For your question, here is the method I provided:

Here's some dummy data

“Table”

Create a new column, [YearMonth], containing the year and month.

YearMonth = FORMAT('Table'[Date], "YYYY-MM")

Create a measure to extract the last date of each month.

LastDateOfMonth = 
CALCULATE(
    MAX('Table'[Date]),
    ALLEXCEPT('Table', 'Table'[YearMonth])
)

Create two measures to calculate the totals of "Nike" and "Macron" respectively.

Nike TotalValueAtMonthEnd = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Date] = 'Table'[LastDateOfMonth]
        &&
        CONTAINSSTRING('Table'[Name], "Nike")
    )
)

Macron TotalValueAtMonthEnd = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Date] = 'Table'[LastDateOfMonth]
        &&
        CONTAINSSTRING('Table'[Name], "Macron")
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

I am happy to help but please provide data as table that we can import.

I dont want to faff reformatting your text data. Thank you

You can do this using the table icon

or better still save the data with read access on Onedrive, Dropbox or Google and post the url link here