Forum Discussion
Helper IVNeed to show 'week number' without changing aggregation.
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
Microsoft EmployeeThe 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
diogobraga2Thanks 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
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