cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Patron
Post Patron

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

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:

1.PNG

 

And here is an example of the ClaimData table:

2.PNG

 

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:

3.PNG

 

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.

4.PNG

 

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

1 ACCEPTED SOLUTION

@AW1976NOVA 

 

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

View solution in original post

6 REPLIES 6
Super User I
Super User I

Hello @AW1976NOVA 

 

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.

 

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?
 
5.PNG

@AW1976NOVA 

 

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

View solution in original post

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

@AW1976NOVA 

Glad it worked.

@AW1976NOVA 

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors