Forum Discussion

dBrand's avatar
dBrand
Frequent Visitor
3 years ago

Getting Sum between two dates from two tables broken down by field in one of the tables

I have 3 tables:
Table1 - Transactions
Table2 - Payments
Table3 - Date Table

 

Relationships:
Table1.DocDate <- Table3.Date
Table2.PmtDate <- Table3.DATEADD (inactive)
Table1.DocNo <-> Table2.DocNo

 

Looking to have a Matrix Showing, by Cust Class (which is only in Table1):
Total Sales (from Table1)
Total Pmts (from Table2)
Pmts 30-60 days old (from todays date) for that line, which is for that Customer Class
Pmts 61-90 days old (from todays date) for that line, which is for that Customer Class
Pmts Over 90 days old (from todays date) for that line, which is for that Customer Class

 

I get the Cust Class and the Total Sales and the Total Pmts. But the Aging breakdown I seem to be stuck on.

 

My measure for 30-60, for instamce, is:

30-60 Pmts =
VAR StartDate = DATEADD(Dates[Todays Date],-30,DAY)
VAR EndDate = DATEADD(Dates[Todays Date],-60,DAY)
RETURN

CALCULATE(SUM(view_PBI_AR_Dashboard_Aging_Payments[ApplyAmt]), Dates[Date]>=EndDate, Dates[Date]<=StartDate)

 

I have also tried:

 

30-60 Pmts =
VAR StartDate = DATEADD(Dates[Todays Date],-30,DAY)
VAR EndDate = DATEADD(Dates[Todays Date],-60,DAY)
RETURN

CALCULATE(SUM(view_PBI_AR_Dashboard_Aging_Payments[ApplyAmt]),USERELATIONSHIP(Dates[Date],view_PBI_AR_Dashboard_Aging_Payments[GLPostDate]),Dates[Date]>=EndDate, Dates[Date]<=StartDate)

 

I can't seem to get the right numbers. I'm likely going about this wrong, but am a bit lost. Any help would be apprecaited. Thanks!

 

 

1 Reply