How to add two custom columns to a Date table to sum amounts from another field

[Anonymous]

Hi,

I have a manually created Date Table called 'Date Table' and I'd like to add two manually created columns to it.  One column needs to sum the 'ClaimData'[PlanPaid_AnnualTotal] amount based on the 'ClaimData'[PaidDateMMMYY] field while the other needs to sum the 'ClaimData'[PlanPaid_AnnualTotal] amount based on the 'ClaimData'[IncurredFromMMMYY] field.

Here is an example of the Date table:

And here is an example of the ClaimData table:

I need to somehow add two manually created columns to my Date Table:

1)  One column [PaidDateAmount] that will sum the ClaimData[PlanPaid_AnnualTotal] field where 'Date Table'[IncurredDateSort] matches ClaimData[PaidDateMMMYY]. 

2)  And another [IncurredDateAmount] that will sum the ClaimData[PlanPaid_AnnualTotal] field where 'Date Table'[IncurredDateSort] matches ClaimData[IncurredDateMMMYY]. 

Essentially, I want the result of my Date Table to look like this:

I know Date tables are not typically used like this but my project is pretty large and complex as it is.  I only want to create the Date table like this so I can plot these two amounts one a chart visual for a very specific request.

Thank you!  I'll be monitoring the post if you have any questions for me.

[mhossain]

Hello @Anonymous 

So your objective is to bring these two new number columns to chart/table by Date (from date table). Inspite of creating two columns in the data table, try to do below:

1). Create first measure [PaidDateAmount] as

CALCULATE(
sum(ClaimData[PlanPaid_AnnualTotal]), TREATAS (values('Date Table'[IncurredDateSort]),ClaimData[PaidDateMMMYY]))

2). Create second measure [IncurredDateAmount] as

CALCULATE(

sum(ClaimData[PlanPaid_AnnualTotal]), TREATAS (values('Date Table'[IncurredDateSort]),ClaimData[IncurredDateMMMYY]))
 

Thats it, now you can drag your date from the Date table and these two measures.

Note, I typed DAX code in a notepad so might be some spelling mistakes, hope this works for you.

Please let me know if above is working or if you have any further questions.

[Anonymous]

Thank you for the help so far but it isn't working.  I do not have any physical relationships and need to keep it that way.  Here are my two created columns:

IncurredDateAmount =

CALCULATE (

   SUM ( ClaimData[PlanPaid] ),

      TREATAS (

         VALUES ( 'Date Table'[Date] ), ClaimData[IncurredDateMMMYY]

   )

)

PaidDateAmount =

CALCULATE (

   SUM ( ClaimData[PlanPaid] ),

      TREATAS (

         VALUES ( 'Date Table'[Date] ), ClaimData[PaidDateMMMYY]

   )

)

The issue is that for some reason my results for each date are now the same...any ideas?  Maybe we are not using TREATAS the correct way?

[mhossain · Accepted solution]

@Anonymous 

Please create those two new measures and not columns, don't require to create two columns in the DATE table.

Just drag the measures on the Charts/Visuals and date from Date table.

You can share your pbix with dummy data, and I can try to fix that. If sharing the file not possible please try above. Let me know if this works

[Anonymous]

Thank you!  I was very confused.  Thank you so much.

[mhossain]

@Anonymous 

Glad it worked.

[mhossain]

@Anonymous 

In addition to previous comment, I guess there are no relationship between these tables, if you have already created physical relationship between the tables between these two tables in any way, in the same MEASURES you can remove the relationship first in order to get it working