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

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

     

9 Replies

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

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

      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'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.