Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
1 year ago
Solved

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? 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • speedramps's avatar
    speedramps
    Super User
    1 year ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.