Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
M_SBS_6
Helper V
Helper V

Month End Values

 

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? 
1 ACCEPTED SOLUTION
v-nuoc-msft
Community Support
Community Support

Hi @M_SBS_6 

 

For your question, here is the method I provided:

 

Here's some dummy data

 

“Table”

 

vnuocmsft_1-1737097750261.png

 

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.

 

vnuocmsft_2-1737098364305.png

 

Regards,

Nono Chen

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

View solution in original post

2 REPLIES 2
v-nuoc-msft
Community Support
Community Support

Hi @M_SBS_6 

 

For your question, here is the method I provided:

 

Here's some dummy data

 

“Table”

 

vnuocmsft_1-1737097750261.png

 

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.

 

vnuocmsft_2-1737098364305.png

 

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
Super User
Super User

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

speedramps_0-1736965390783.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.