Forum Discussion
Count of Rows based on Calendar Week
Good Morning,
I am new to Power BI and I need to visualize data to compare the document date or Orders and when they were created on in our system by weeks.
Table shows document and created on date, I used a weeknum() funktion to receive the calendar week.
Now I need the count of the Orders per calendar week.
I used this graphic to get the Order Count by Document Calendar Week.
Now I want to enter the count of Orders by Created on Calendar week in the Y-Line of this graphic.
Question:
How do I set up the Measure to count the rows per calendar week?
I thought about Countif() combined with a filter() function but I am not sure how to set this up.
Could you maybe also explain the logic of the measure in your explanation so that I can understand how it works, if possible?
Thanks a lot in advance.
BR
Max
Hi, Jukerino
I briefly simulated some data that I hope fits your situation.
Column:
Weeknum = WEEKNUM([Created Date],2)Measure:
Measure = CALCULATE ( COUNT ( 'Table'[Order] ), FILTER ( ALL ( 'Table' ), [Weeknum] = SELECTEDVALUE ( 'Table'[Weeknum] ) ) )Is this the result you expect? If not, please picture the output you expect.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi,
Create a Calendar Table and write calculated column formulas to extract Year, Month name, Month number and weeknum. Create a relationship between the date column in your raw dataset to the Date column in your Calendar Table. To the X-axis of your visual, drag Year and weeknum from the Calendar Table. Write this measure
Measure = countrows(Data)
Hope this helps.
Hello Ashish,
can you define in more detail how to set up the measure?
I created a date table like the one below with dates till 2023.
Date Year Monthname Month Calendarweek 01.01.2021 2021 Januar 21 1 53 02.01.2021 2021 Januar 21 1 53 03.01.2021 2021 Januar 21 1 53 Relationship between "created on" and date table is there "Date Column" is there.
How do I setup the measure?
I get for Orders per Document Date and Created on Date the same values, but I can see in the raw data that there are differences.
Hi,
I cannot understand your concern. Share the download link of your PBI file and show the expected result.
Hi, Jukerino
I briefly simulated some data that I hope fits your situation.
Column:
Weeknum = WEEKNUM([Created Date],2)Measure:
Measure = CALCULATE ( COUNT ( 'Table'[Order] ), FILTER ( ALL ( 'Table' ), [Weeknum] = SELECTEDVALUE ( 'Table'[Weeknum] ) ) )Is this the result you expect? If not, please picture the output you expect.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
