How to link tables or create measure based on date?

[General Ledger]

I have two tables of transactions: one is Charges and the other is Payments. Each table has a Date of Service field.

1. I am trying to create a PivotTable that shows Charges and Payments by Date of Service.
2. I also want to show Payments as a % of Charges.
3. I also want to group on Year and Month on Date of Service. I have a Date table linked to the Charges and a Date table linked to the Payment tables.

How do I relate the tables or create measures do this?

Year Month Charges Payments Percent
YYYY MMMM $$$$$$ $$$$$$$$ %%%


Very new newbie to Power Pivot. Using Excel 2010.

Thank you very much,

G/L

 

[scottsen]

You make it sounds like you have TWO Date tables, is that true? What you want is 1 data table that filters both your Charges and Payments.

 

[Kazlik]

If you have created relationships on dates between your 3 tables then you can add a Charge Amount and Payment Amount calculated column to your Date table with RELATED. From there you can make your measures as needed. I am sure there is an easier way but the measures below worked in a test file I made. If you have blanks in your payments you can adjust the Pivot Table options to show a 0 in empty cells and then your percent will be zero.


Charge Amount
=RELATED(Charges[Amount])
Payment Amount
=RELATED(Payments[Amount])


Charges:=SUM(Date[Charge Amount])
Payments:=SUM(DatePayment Amount])
Percent:=DIVIDE([Payments],[Charges])


If you need to group on Year and Month on Date of Service you can make calculated columns for Month and Year.
=MONTH(Date[Date of service]) and =Year(Date[Date of service]) and then in your pivot table have Year, Month and Date of service are your rows.

 

[Kazlik]

If you have many transactions per date and your relatioships as such then you should be able to just have 3 measures like these.



Charges:=SUM(Charges[Amount])
Payments:=SUM(Payments[Amount])
Percent4:=DIVIDE([Payments],[Charges])