cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

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?

1 ACCEPTED SOLUTION
Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

5 REPLIES 5
Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Helper IV

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.

Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Super User

@diogobraga2 , It may be because you have data across years and it is summing up that.

Have week year and try

refer

Helper IV

Hi @amitchandak! Adding years did not change the result. Any other suggestion? Thank you.