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

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