Forum Discussion

diogobraga2's avatar
diogobraga2
Icon for Helper IV rankHelper IV
6 years ago
Solved

Need to show 'week number' without changing aggregation.

The goal is to show week number in the visualization next to the weekly Total Returning trend. However, when 'Date' is removed from the visual the Total Returning aggregation changes. See screenshots below. What am I missing? 

 

PBIX LINK 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    The problem is the measure.  It evaluates if the min of the week is Sunday.  At the day level that returns just Sundays in the visual.  When just the Week is present, it is still true but it then uses all the days of that week in the calculation.  Please use this expression which gets same results whether Date is there or not.

     

    Total Returning W =
    VAR thismin =
    MIN ( 'Dates Adj'[Date] )
    RETURN
    IF (
    WEEKDAY ( MIN ( 'Dates Adj'[Date] ) ) = 1,
    CALCULATE ( SUM ( [COMMITMENTS] ), 'Dates Adj'[Date] = thismin )
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    The problem is the measure.  It evaluates if the min of the week is Sunday.  At the day level that returns just Sundays in the visual.  When just the Week is present, it is still true but it then uses all the days of that week in the calculation.  Please use this expression which gets same results whether Date is there or not.

     

    Total Returning W =
    VAR thismin =
    MIN ( 'Dates Adj'[Date] )
    RETURN
    IF (
    WEEKDAY ( MIN ( 'Dates Adj'[Date] ) ) = 1,
    CALCULATE ( SUM ( [COMMITMENTS] ), 'Dates Adj'[Date] = thismin )
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • diogobraga2's avatar
      diogobraga2
      Icon for Helper IV rankHelper IV
      6 years ago

      Thanks mahoneypat! Can you help me understand what this calculation does? I applied to the sample dataset and it worked. On the real dataset is not working yet, the only difference I am aware is that the real dataset has other dimensions such as AcademicYear and GradeLevel. I do not why see that would be an issue, and as of now cannot identify any other difference. Can you think of  anything for me to check? Thanks again. 

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        The measure first takes the min date value in the context.  When the Date column is in the visual, that is the result.  When it is just week, since the default min value of any week is Sunday (Weekday =1), it still works.  The change is that it now uses that min value to filter the date table before calculating the result.  Any other columns in the visual that affect the Date table would also affect the result.

         

        When you say it is not working, what do you mean?  Is it blank?  Wrong numbers?  Either way, provide more details on the other columns in the visual.  You can try removing them one by one to figure out which one is throwing it off.

         

        Regards,

        Pat