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 w...
Need Help
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 

 

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?