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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 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. 

     

     

     

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Jiangmei_wu ,

     

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

    Best Regards,

    Neeko Tang

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

  • Jiangmei_wu's avatar
    Jiangmei_wu
    Frequent Visitor
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 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. 

       

       

       

      • Jiangmei_wu's avatar
        Jiangmei_wu
        Frequent Visitor
        3 years ago

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