Count by Week with Live Connection

[StephenGW]

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

[v-janeyg-msft · Accepted solution]

Hi, @StephenGW 

You can refer to the tutorial to create weeknum in power app. 

Solved: How to calculate week number? - Power Platform Community (microsoft.com)

Solved: Calculate number of week - Power Platform Community (microsoft.com)

It should be ok if you check carefully.

Best Regards

Janey Guo

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

[StephenGW]

@v-janeyg-msft 

This will work perfectly! Thank you for the solution!

[amitchandak]

@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-La...
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]

@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?

[StephenGW]

@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