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

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