Forum Discussion

Jiangmei_wu's avatar
Jiangmei_wu
Frequent Visitor
3 years ago
Solved

Count current week and previous week

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.

 

nameDate 1Week DateWeek numberWeek Starts
JohnJune 15, 2023Thu24June 11, 2023
AnnJune 16, 2023Fri24June 11, 2023
AlexJune 12, 2023Mon24June 11, 2023
SmithMarch 10, 2023Fri10March 5, 2023
AnnMarch 10, 2023Fri10March 5, 2023
JohnMarch 15, 2023Wed11March 12, 2023
SmithMarch 14, 2023Tue11March 12, 2023
RobMarch 14, 2023Tue11March 12, 2023
JamesMarch 14, 2023Tue11March 12, 2023
JayMarch 15, 2023Wed11March 12, 2023
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

     

     

     

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

     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's avatar
      Jiangmei_wu
      Frequent Visitor
      3 years ago

      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). 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 

    • Jiangmei_wu's avatar
      Jiangmei_wu
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.