Forum Discussion

StephenGW's avatar
StephenGW
Helper II
5 years ago
Solved

Count by Week with Live Connection

Hello all,

 

I have some data that is linked live so there is no table in BI to add a calculated column to. It does have a date column so it automatically adds a year and month data choice but no week. I would like to count a column UserName weekly. So how many times Bob showed up last week and then again this week and so on into the future. Can anybody think of a way to do this? I tried creating a Week data point using a measure like this =WEEKNUM(SELECTEDVALUE(Table[Date])) but I can't seem to make a table or chart that uses this correctly.

 

Any ideas?

 

Thanks

StephenGW

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    StephenGW , The best is having weeks column at the source. Or try measures like below

    In this case, you need a week and week rank column at your source at you are using the live connection.

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

     

     

    Not as you do not have you can have that around today or selected date

     

     

    Week =
    var _max1 = maxx(allselected('Date'), 'Date'[Date]) // or _max = today()
    var _stweek = _max1 +-1*WEEKDAY(_max1,2)+1
    var _edweek= _max1+ 7-1*WEEKDAY(_max1,2)
    return
    calculate(sum(Table[Value]), filter(date, Date[Date] >=_stweek && Date[Date] <=_edweek))
    // or use all date
    //calculate(sum(Table[Value]), filter(all(date), Date[Date] >=_stweek && Date[Date] <=_edweek))

     

    last week =
    var _max1 = maxx(allselected('Date'), 'Date'[Date]) // or _max = today()
    var _stweek = _max1 +-1*WEEKDAY(_max1,2)+1 -7
    var _edweek= _max1+ 7-1*WEEKDAY(_max1,2) -7
    return
    calculate(sum(Table[Value]), filter(all(Date), Date[Date] >=_stweek && Date[Date] <=_edweek))

    • StephenGW's avatar
      StephenGW
      Helper II
      5 years ago

      amitchandak 

       

      My data is input by a Power App which will not work with any formulae in the table and I don't want to have the user choose a week number. I might look into having the Power App convert the date and put it in a column as right now my data is not too large to go back and amend the old entries. I will play with your suggestions for a short time and see if I can make it work.

       

      Thanks,

      StephenGW

    • StephenGW's avatar
      StephenGW
      Helper II
      5 years ago

      amitchandak 

       

      I went and looked at Power Apps and it does not have a WeekNum function so having the app output the week number is not as easy as I hoped.

       

      I then came back to your suggestion and noticed I did not explain my problem well enough. I want to be able to chart the count for every week in a year so weeks 1 through 52 and each one have a count for that week in a chart. I think I need a way to add a week number column to my data with a measure is that possbile?