Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
diogobraga2
Helper IV
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? 

 

PBIX LINK 

 

2020-08-24_12-27-50.png2020-08-24_12-29-15.png

1 ACCEPTED SOLUTION
mahoneypat
Employee
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
mahoneypat
Employee
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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. 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User
Super User

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.