Every Month last day value

[Varan_15]

Hi All,

I have daily data need to calculate every month last date value i did using below measure it's working fine, But if i select multiple month on slicer it should sum of selected month last day values.

 Measure  = VAR last = LASTDATE(Sales[Days])

RETURN

CALCULATE(SUM(Sales[Saving]),'Calendar'[Date]=last)

Please suggest how to achieve

Thanks in advance 

MS

[rajendraongole1]

Hi @Varan_15 - can you try below measure to calculate last day for selected months

Measure =
VAR LastDayOfEachMonth =
SUMMARIZE(
'Calendar',
'Calendar'[YearMonth], // Assuming you have a YearMonth column in your Calendar table
"LastDay", MAX('Calendar'[Date])
)
RETURN
SUMX(
LastDayOfEachMonth,
CALCULATE(SUM(Sales[Saving]), 'Calendar'[Date] = [LastDay])
)

Hope it works.

[Varan_15]

@rajendraongole1 ,

Thanks for the update,  As i have 5 columns in calander table and i tried above same measure but it's showing error like "LastDay" column cannot be found.

error: Column 'LastDay' cannot be found or may not be used in this expression.

Regards,

MS

[rajendraongole1]

Hi @Varan_15 - can you please share sample data as well in text format. will check at our end. 

i have modified to take reference from calendar table. 

Measure =
VAR SelectedMonths = VALUES('Calendar'[YearMonth])
VAR LastDays =
SUMMARIZE(
FILTER(
ALL('Calendar'),
'Calendar'[YearMonth] IN SelectedMonths
),
'Calendar'[YearMonth],
"LastDay", MAX('Calendar'[Date])
)
VAR Result =
SUMX(
LastDays,
CALCULATE(
SUM(Sales[Saving]),
Sales[Days] = [LastDay]
)
)
RETURN
Result

can you check above still error, please share sample copy of data(dummy) text copy . 

Hope it works. 

[Varan_15]

@rajendraongole1 ,

Still same error "Column 'LastDay' cannot be found or may not be used in this expression."

attached sample data here:

Day    Area Branch Code Saving
16-May-24 CAPITAL 101 28.17
23-May-24 CAPITAL 101 29.22
30-May-24 CAPITAL 101 28.83
06-Jun-24 CAPITAL 101 26.73
13-Jun-24 CAPITAL 101 24.53
20-Jun-24 CAPITAL 101 23.53
30-Jun-24 CAPITAL 101 23.84

[rajendraongole1]

Hi @Varan_15 - Thanks for sharing, 

Monthly Last Day Saving =

VAR LastDayPerMonth =

    SUMMARIZE(

        LDY, // Replace 'LDY' with your actual table name

        LDY[Area],

        LDY[Branch],

        "LastDay", MAX(LDY[Day])

    )

VAR SumOfSavings =

    SUMX(

        LastDayPerMonth,

        VAR LastDayValue = [LastDay]

        RETURN

            CALCULATE(

                SUM(LDY[Code Saving]),

                LDY[Day] = LastDayValue

            )

    )

RETURN

SumOfSavings

can you check the below measure: rename your table name and coumns

Hope it works

[Varan_15]

@rajendraongole1 ,

Thanks for your effort.

it's fetching month last day as expected but if i select MAY and JUN month using slicer then it should add total value ( 30th May value + 30th June value ) = 28.83+23.84  so total will be 52.67

based on month selection it should be sum of value.

Thanks,

MS

[rajendraongole1]

Hi @Varan_15 - create below two measure, one for last day of month another summation of each month selection.

replace table name as per your model.

Measure 1:

LastDayOfMonth =

CALCULATE(

    MAX('LDY'[Day]),

    ALLEXCEPT('LDY', 'LDY'[Area], 'LDY'[Branch], 'LDY'[Day])

)

Measure 2:

SumOfLastDayValues1 =

SUMX(

    VALUES('LDY'[Day]),

    VAR LastDay =

        CALCULATE(

            MAX('LDY'[Day]),

            FILTER(

                ALL('LDY'),

                'LDY'[Day] <= MAX('LDY'[Day]) &&

                MONTH('LDY'[Day]) = MONTH(MAX('LDY'[Day])) &&

                YEAR('LDY'[Day]) = YEAR(MAX('LDY'[Day]))

            )

        )

    RETURN

    IF(

        'LDY'[Day] = LastDay,

        SUM('LDY'[Code Saving]),

        0

    )

)

Output:

if we select June 30

if we select june and may below is the result.

Hope this works.

[Varan_15]

@rajendraongole1 ,

Thanks for your update, Please note the slicer will be monthly only not daily so as per your measure if i select month then it's calculating all the day of that particular month hence the answer is worng.

if i select May month then it's appearing 86.22 which is sum of all the days . please suggest

Thanks in advance

[rajendraongole1 · Accepted solution]

Yes @Varan_15 - because of summation we are getting 86.22

monthname = FORMAT(LDY[Day],"mmm")

Instead of that, you can convert the formaule by averagex and try to check the result

if it is monthly selection , what is expections.

Hope it helps