Need a way to display the starting day of the week for week numbers in 2023 and 2024

[ammarahw]

Below is a picture of what is currently happening with my pivot table. I created a field called Week Start Date that I made with the calculation:

weekstart(report_date)

Is there a way to get that formula to work for just 2023 or just 2024 so that I can have the week start dates beside the week number column to indicate what the starting day for the week was in 2023 and in 2024 without creating two rows?

 

[mpc]

Hi, 

If you don't want to add a Year dimension as parent of Week, you should add a Set Analysis to your measures. 

Kind regards

[marcus_sommer]

The weekstart has not an added value in such object - therefore remove it and just use the week.

[ammarahw]

I am looking for the solution that would allow me to display the Week Number, then the week start date in 2023, then the week start date in 2024 as their own columns. 

[ammarahw]

I'd like to display the week start date for 2023 and 2024 so that the doesn't need to figure out what part of the year they are considering when looking at the week number. 

I was hoping to be able to display week number, then the 2023 week start date, then the 2024 week start date so that when  a user is looking a the table, they'll see the week number and then the week we are considering in both years. 

[ammarahw]

I have an update. I created a measure using weekstart([report_date.autoCalendar.Week]) and it does give the dates side by side like I initially wanted but the dates it shows are incorrect. 

When I try the calculation weekstart([report_date]) it just goes blank. 

Does anyone have any insight into how I can get the measure to work?

[marcus_sommer]

Without knowing your data + data-model + object-dimensions in more details it's not possible to say why the weekstarts are wrong.

IMO it's not possible nor sensible to get the view like you described. If you put the weekstart as dimension in the chart - below the week or even without another dimension - you will always get a separat row for each weekstart, like you have shown within the origin question. The values are different and therefore they must be displayed separately.

If you transfers the weekstart into the measures it should be working to display n weekstarts side by side in regard to the week. That's not working in your case means that there is any mistake within the data and/or data-model and/or object-dimensions - which could be surely fixed. But this wouldn't lead to a sensible view because these weekstarts are now part of the measures and will disturb the real purpose of the view which is surely to show any sum/count/rate of measures.

If you really want to include everything within a single dimension-value you will need to aggregate these information - within the script or with a calculated dimension, maybe like:

Week & ' - ' & aggr(concat(distinct weekstart(MyDate), ' + '), Week)

but it will only clutter the object without a real added value.

To interpret data on a weekly level the user don't need to know the exact weekstarts and their moving between the years. If they users don't have an approximately feeling to which quarter/month a week belonged they couldn't interpret the view in any way - and should be trained to get this knowledge.

[ammarahw · Accepted solution]

I ended up being able to do it. I created a measure called Week Start with the calculation min(date_field) and then added it to the pivot table as a measure.