count of max available date of each month

[Rama1]

hi,

Need some help to count of max available date of each month

In my Pivot table i have 2 dimensions as rows office and company and 1 dimension as column date  and and 1 measure count(Office). and i created a new column month by using =date(monthstart(Date1,), 'MMM-YY') as i don't have month in my data (my source data is excel sheet). Now how can i calculate the max available date count of each month of each office.

my Pivot table look like this:

my expression should pick max week(available date) of each month and show only that week count in the table.

Thanks in advance.

[Spivey]

If I'm understanding correctly, you want for only the max WeekStart for each month to show up as a column?

This is what you should use as the Week field:

aggr(max(WeekStart(TransDate)), "=MonthName(TransDate)")

 ....where TransDate is your Available Date.

[Rama1]

Hi Spivey,

Thank you so much for your reply.

aggr(max(WeekStart(TransDate)), "=MonthName(TransDate)")

This resolves half of my requirement but it is showing the complete count of the that month with max week of the month.

i just need the count for only max week but not for all the weeks together in that month.

Thanks

Rama

[edwin]

i would add a flag for the last week of the month in the script.  that way you dont need complex set analysis in your expression.  that is assuming the last week of the month does not change (bec it could if the user is allowed to select only a part of the month)

[edwin]

of course the flag should be in the calendar

[Spivey · Accepted solution]

Format your measure like this:

=Count({<"=WeekStart(TransDate)"={"=aggr(max(TransWeek), ""=MonthName(TransDate)"")"}>} Office)

[Rama1]

Hi Spivey,

Thank you so much for response and help.

the above expression resolves my problem and i am able to pick the WeekStart of the each month. Could you also let me know how to get Weekend of each month For ex: If there are 4 weeks in a month i need count for the last week instead of the first. 

Thanks in advance.