how to get count for the last week 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 expression should pick max week(available date) of each month and show only that week count in the table.

with this i am getting the count for the 1st of each month but i need count for the last week of each month.

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

Thanks

Rama

[arulsettu]

Can you share a sample app with expected output

[Rama1]

This is my pivot table and i have Company ,Office as my rows and date and month as my columns (dimensions) and my measure is count(Office).  i don't have any month filed in my data so i am getting the month field in        set analysis with the expression:

=date(monthend(Mydatefield,), 'MMM-YY')

Here with this expression with date i am getting the last week of the each month :

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

and with this expression i am getting the count of first week of the month

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

My output should be the i need count for the last week of the month and not first week of the month.

Thanks in advance.