Aggregate Past Working Days Sales

[sogloqlik]

Hi.

Need Some Help.

I have two Tables (See Example File Attached):

A Fact Table which contains the Sales data and A Calendar Table. The two tables are connected through the Date field.

Now, I have to Calculate the Daily Sales Rate up to date and to present it in a Pivot table for a Customer, Sales Manager and Sales Area Manager. the formula is the sales(fact table) in a chosen period divided by the past working days(Calendar Table),

For a single month there is no problem but if I Want to choose the current month and the previous month, something doesn't work. 

In the Example: For John, For the 30.12.2018, when the selected Year-Month is 11-12/2018 the Daily rate should be:

(1000+500)/(21+21)=35.714

and for the 31.12.2018 it should be

(1000+500)/(21+22)=34.88

An Advice would be appreciated.

Motty

[baarathi]

@sogloqlik The date field in Sales table and calendar table should have equivalent values in both the tables. Or else we need implement a master table.

[sogloqlik]

I'm not sure what you mean in Master Table.

and The dates values in the Calendar table  are derived from the fact dates.

The main problem i have is when the Todaypast workingdays of the current month equals to that of the month before.

[baarathi]

I am sorry, what i meant was master calendar.

[sogloqlik]

the calnedar is a Master calendar.

or do you mean a  Master calendar which is not connected to the fact?

[sogloqlik · Accepted solution]

Solved

[baarathi]

How did you solve it?