If max date then sum divide by weekday number else sum divide by 5

[JayKay07]

Hello,

Can anyone please help me to write formula. 

I have table chart with two columns: Date and Tickets

I want to calculate if it's max date then sum([Tickets])/num(Weekday(today())) else sum([Tickets])/5)

Below is the formula that I wrote, but it is wrong.  It always gives the result from first condition.

IF(max([Date.autoCalendar.Date])=max([Date.autoCalendar.Date]), sum([Tickets])/num(Weekday(today())), sum([Tickets])/5)

[anthonyj]

Hi @JayKay07 ,

I think that due to the table including [Date] that there is an error in the aggregation. Max(Date) when you have date as a dimension will always be the same as Date. You need Max(Date) to be across the whole set of dates ignoring the [Date] dimension. Try like below:

IF([Date.autoCalendar.Date]=max( TOTAL [Date.autoCalendar.Date]), sum([Tickets])/num(Weekday(today())), sum([Tickets])/5)

This will depend on whether you need max date across the whole set or if there is a subset of max dates. For example: if there are many dates within a ticket dimension and you want this max date then you need a total function like this:

max( TOTAL <Tickets> [Date.autoCalendar.Date])

Thanks

Anthony

[JayKay07]

Thanks for reply. Still not working. On the most recent date it is still dividing by 5.

[Sivapriya_d · Accepted solution]

Plz check if this helps
if(Count({<[Date]={"$(=max([Date]))"}>} distinct [Date])=1,sum([Tickets])/num(Weekday(today())),sum([Tickets])/5)

[JayKay07]

Thank you