Forum Discussion

ropathak's avatar
ropathak
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

Sum over month from daily data

I have the following data:

It has daily data for each month starting from March 20. I want to calculate the monthly sum of sessions. For example, total number of sessions for month of March = 678 + 4061 + 5170 + ... + 4846 = 54237


Could you please help me with how I can create a new column/measure for the above? Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  ropathak ,

    Here are the steps you can follow：

    Create calculated column:

    Sum_Column =
CALCULATE(SUM('Table'[Session]),
FILTER('Table',
YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER(('Table'[Date])))))

    Result:

    Create measure:

    Sum_Measure =
SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX(('Table'[Date])))),'Table'[Session])

    Result:

     

    Best Regards,

    Liu Yang

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

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    ropathak 

    Create a measure

    Total Sessions = SUM ( Tablename[Sessions])

    Method 1 :
    You can add two columns in PQ or Power BI as Year and Month then Add Year, month and above measure to a Table visual.

    Methos 2:
    I would like to recommend you to use a Dates table and create a relationship (one to Many) between the Dates table and your Data table then Add the Year and Month from the dates table to a Table visual and the above measure.

    Script to create a new Dates table (Add New Table under the Modeling Tab)

    Dates = 
ADDCOLUMNS(
    CALENDAR("01/01/2020","31/12/2021"),
    "Month", MONTH([Date]),
    "Month Name", FORMAT([Date],"Mmmm"),
    "Year", YEAR([Date]),
    "Year Month No", YEAR([Date]) * 100  + MONTH([Date]),
    "Year Month" , FORMAT([Date] , "Mmm yyyy" )
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  ropathak ,

    Here are the steps you can follow：

    Create calculated column:

    Sum_Column =
CALCULATE(SUM('Table'[Session]),
FILTER('Table',
YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER(('Table'[Date])))))

    Result:

    Create measure:

    Sum_Measure =
SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX(('Table'[Date])))),'Table'[Session])

    Result:

     

    Best Regards,

    Liu Yang

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