Count current week and previous week

[Jiangmei_wu]

Hi there,

I have to count current week and previous week using a visualization card each week. here is the excel file. The Dax I wrote for previous week gave me a blank. for current week, I simplely used count function and it seems work. There is a slicer in the dashboard used Week Starts column. In the Date 1 column, some of dates have hours 00 behind. Can you guide me? Thanks.

name	Date 1	Week Date	Week number	Week Starts
John	June 15, 2023	Thu	24	June 11, 2023
Ann	June 16, 2023	Fri	24	June 11, 2023
Alex	June 12, 2023	Mon	24	June 11, 2023
Smith	March 10, 2023	Fri	10	March 5, 2023
Ann	March 10, 2023	Fri	10	March 5, 2023
John	March 15, 2023	Wed	11	March 12, 2023
Smith	March 14, 2023	Tue	11	March 12, 2023
Rob	March 14, 2023	Tue	11	March 12, 2023
James	March 14, 2023	Tue	11	March 12, 2023
Jay	March 15, 2023	Wed	11	March 12, 2023

[Anonymous · Accepted solution]

Hi @Jiangmei_wu ,

Create this measure.

Measure = var a=SUMMARIZE(ALLSELECTED(Sheet1),Sheet1[location],[Week number],[Date 1],"Current Week Count",[Current Week Count])

return MAXX(FILTER(a,[location] in VALUES(Sheet1[location])&&[Week number] in VALUES(Sheet1[Week number])),[Current Week Count])

Measure 2 = SUMX(VALUES('Sheet1'[location]),[Measure])

Best Regards,

Neeko Tang

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

[Anonymous]

Hi @Jiangmei_wu ,

According to your description, here are my steps you can follow as a solution.

(1)My test data is the same as yours.

(2) We can create measures. 

current week = COUNT('Table'[name])

previous week = COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Week number]=MAX('Table'[Week number])-1))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[MeiJiang]

Here is Excel file and pbix. Hope you could open them.

Sample 1.pbix

Sample Raw Data.xlsx

[Jiangmei_wu]

Hi Neeko,

Thanks for provide the solutions!

I have a version Power BI Desktop (May, 2022). The current week Dax works, but Previous Week Dax still gave me  a blank in the card. I twicked a bit to 

Previous Week Count = calculate(COUNT(Sheet1[Name]),

filter(all(Sheet1), 'Sheet1'[Week number]=max(Sheet1[Week number])-1)) and It works. Under Power BI Desktop, yours works.

Now I have a new issue. When I put eveything in a table, the previous week isn't correet using both Daxs. I also need help on Daily Highest Name in Current Week.

How can I add the attachment for raw data and pbix?

Thanks,

Mei

[Anonymous]

Hi @Jiangmei_wu ,

Update the measure.

measure1 = calculate(COUNT(Sheet1[Name]),
filter(all(Sheet1), 'Sheet1'[location]=MAX('Sheet1'[location]) && 'Sheet1'[Week number]=max(Sheet1[Week number])-1))

Previous Week Count = SUMX(SUMMARIZE('Sheet1','Sheet1'[location],'Sheet1'[Week number],"total",[measure1]),[total])

Daily Highest Name Count in Current Week = 
MAXX(SUMMARIZE(ALL('Sheet1'),'Sheet1'[location],"count",[Current Week Count]),[Current Week Count])

If the above one can't help you get the desired result, please provide your expected result with backend logic and special examples. 

Best Regards,

Neeko Tang

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

[Jiangmei_wu]

Hi Neeco, thank you for your time and patient to help me! Now Previous Week Count is working properly. The daily highest count by location in current week didn't have the result I am looking for in the Table (in the card it is correct). Using Week number 11 as current week which is March 12, 2023 that week in Slicer. In Excel Pivot table, the highest number at Location1 is 3, at Location3 is 4. However, pbix shows 7 for both locations. Plesae see the screenshots below and file links.

Sample Raw Data - Copy.xlsx

Sample 1 (1).pbix

[Anonymous · Accepted solution]

Hi @Jiangmei_wu ,

Create this measure.

Measure = var a=SUMMARIZE(ALLSELECTED(Sheet1),Sheet1[location],[Week number],[Date 1],"Current Week Count",[Current Week Count])

return MAXX(FILTER(a,[location] in VALUES(Sheet1[location])&&[Week number] in VALUES(Sheet1[Week number])),[Current Week Count])

Measure 2 = SUMX(VALUES('Sheet1'[location]),[Measure])

Best Regards,

Neeko Tang

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

[Jiangmei_wu]

One small thing - can the Daily highest number in the current week in Card be 4 instead of 7 (total)?

[Anonymous · Accepted solution]

Hi @Jiangmei_wu ,

You just need to place [MEASURE] on the card visual object.

Best Regards,

Neeko Tang

[Greg_Deckler]

 Couple ways to potentially make this easier. One, use Power Query to create a week offset column: 

InsertWeekOffset = Table.AddColumn(InsertWeeknYear, "CurrWeekOffset", each (Number.From(Date.StartOfWeek([Date], Day.Monday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/7, type number),

Shout out to Melissa de Korte for the code.

Another way would be Sequential: https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116

@Jiangmei_wu

[Jiangmei_wu]

Greg, sorry, I misled you. my question is how to count each employee each week showed up how many times for current week and previous week (two dax - one for current week and one for previous week).