Forum Discussion

NewbieJono's avatar
NewbieJono
Icon for Post Partisan rankPost Partisan
2 years ago
Solved

Calculate volume for max month

is there any method to calculate the total volume for the whole month based on the last date in a table

 

e.g

 

if the data for april went up to 27th April. i would like to calculate the volume for all dates in april.

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi NewbieJono 
    You can use something like :

    last month sales =
    VAR
    max_Date =
    CALCULATE(MAX('orders'[Order Date]),REMOVEFILTERS('Calendar'))
    RETURN
    CALCULATE([total_sales],YEAR('Calendar'[Date])= year(max_Date) && month('Calendar'[Date])= month(max_Date))

    the pbix is attached 

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

2 Replies

  • Alex87's avatar
    Alex87
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hello NewbieJono ,

    Please try the following DAX measure:

    TotalVolumeForMonth = 

VAR _LastDateInMonth = MAX('Table'[Date])  // Assuming 'Date' is your date column and it is properly formatted as a date.
VAR _StartOfMonth = STARTOFMONTH('Table'[Date])
VAR _Result = 
CALCULATE(
    SUM('Table'[Volume]),  
    'Table'[Date] >= _StartOfMonth && 'Table'[Date] <= _LastDateInMonth
)
RETURN
_Result

     If it satisfies your needs, please do not forget to mark my reply as the solution. Cheers!

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

    Hi NewbieJono 
    You can use something like :

    last month sales =
    VAR
    max_Date =
    CALCULATE(MAX('orders'[Order Date]),REMOVEFILTERS('Calendar'))
    RETURN
    CALCULATE([total_sales],YEAR('Calendar'[Date])= year(max_Date) && month('Calendar'[Date])= month(max_Date))

    the pbix is attached 

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.