Forum Discussion

maneschr2022's avatar
maneschr2022
Helper II
4 years ago
Solved

How to count values per week

Hi, I would like to create a measure that allows me to count a total value per week from a column called ID . I created a column that`s called Week of the month, and shows the week number on each month.  the week numbers are 1 to 4 or 5 on each Month as I consider it more readable.

 

Thanks a lot for the help!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi maneschr2022 ，

     

    Not sure what your data table looks like, but if you want to create count a total value per week on your description, you could try creating a measure like this.

    Total = CALCULATE(SUM('Table'[ID]))

    Then drag it into the visual along with the calculated columns in the date table.

    Week of the month = 
VAR _monthnum = MONTH('Date'[Date])
VAR _month = 
SWITCH(TRUE(),
_monthnum=1,"Jan",
_monthnum=2,"Feb",
_monthnum=3,"Mar",
_monthnum=4,"Apr",
_monthnum=5,"May",
_monthnum=6,"Jun",
_monthnum=7,"Jul",
_monthnum=8,"Aug",
_monthnum=9,"Sept",
_monthnum=10,"Oct",
_monthnum=11,"Nov",
_monthnum=12,"Dec"
)
VAR _weeknum =INT(DAY('Date'[Date])/7+1)
RETURN
"Week "&_weeknum&" of the "&_month

    If this doesn't work for you or I misunderstand your needs, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

     

    Best Regards,
    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Create a relationship from the week column in your base data table to the week column of the Calendar table.  To your visual, drag the week column from the Calendar Table.  Write this measure

    Total = sum(Data[Amount])

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi maneschr2022 ，

     

    Not sure what your data table looks like, but if you want to create count a total value per week on your description, you could try creating a measure like this.

    Total = CALCULATE(SUM('Table'[ID]))

    Then drag it into the visual along with the calculated columns in the date table.

    Week of the month = 
VAR _monthnum = MONTH('Date'[Date])
VAR _month = 
SWITCH(TRUE(),
_monthnum=1,"Jan",
_monthnum=2,"Feb",
_monthnum=3,"Mar",
_monthnum=4,"Apr",
_monthnum=5,"May",
_monthnum=6,"Jun",
_monthnum=7,"Jul",
_monthnum=8,"Aug",
_monthnum=9,"Sept",
_monthnum=10,"Oct",
_monthnum=11,"Nov",
_monthnum=12,"Dec"
)
VAR _weeknum =INT(DAY('Date'[Date])/7+1)
RETURN
"Week "&_weeknum&" of the "&_month

    If this doesn't work for you or I misunderstand your needs, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

     

    Best Regards,
    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data