Forum Discussion

Varan_15's avatar
Varan_15
Icon for Helper III rankHelper III
2 years ago
Solved

Every Month last day value

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 ...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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

     

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
2 years ago

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's avatar
Varan_15
Icon for Helper III rankHelper III
2 years ago

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's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Varan_15
      Icon for Helper III rankHelper III
      2 years ago

      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's avatar
        rajendraongole1
        Icon for Super User rankSuper User
        2 years ago

        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.