DAX - Allocate Program Type

[Anonymous]

I am looking for some DAX syntax with the following logic

Table A - 

Columns - Work Order , Date Rec'd. Contract #, Net tons

Table B

Columns - Contract #, Net Tons, Program type

I am trying to allocate Program type to table A...the problem I'm having is Example - Contract # P-1234 has 10 work orders of X amount of tons and only a portion of those work orders should be allocated to a specfic progam.

Anyone have any thoughts....?

[Anonymous]

Any takers? I can provide further information if needed. 

[v-cherch-msft]

Hi @Anonymous

Could you share your sample data and expected output which could reproduce your scenario so that we could help further on it?You can also upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

How to Get Your Question Answered Quickly

Regards,

Cherie

[Anonymous]

@v-cherch-msft

[Anonymous]

@v-cherch-msft above is a sample of the two tables and desired results. Apologies, I clicked post a little too quickly. 

[v-cherch-msft]

Hi @Anonymous

You may link the two table first. Then you may create the calculated columns like below. Attached the sample file for your reference.

Regards,

Cherie

[Anonymous]

@v-cherch-msft one last twist...:) In our line of business we recieve multiple deliveries on the same day....We have a unique ID (work order #) for each load. How would we work around this? 

The function created in the attachment doesn't seem to calculate correctly given duplicate dates. 

[v-cherch-msft]

Hi @Anonymous

The way around this would be to create an index column to provide the uniqueness of rows. Use the index column in calculated column instead of the date column.If you need further help,please share a simplified data sample.

Regards,

Cherie

[Anonymous]

@v-cherch-msft - What would be the best way to index this? I tried to index by work order# but they are not sequenitally related to a specfic contract or date. 

[v-cherch-msft]

Hi @Anonymous

It's always best to post your data in text/tabular format in addition to a screen capture. People trying to help can then readily copy the sample data and run some tests if they need to.

Based on my test,the above formula can be used for my scenario as below.Attached the sample file.Please let me know what's the issue for your data.

Regards,

Cherie

[Anonymous]

Attached is were the problem in the last total is not allowing for proper allocation of the proram amount. I think it has to do with date receive. What would you recommend? @v-cherch-msft

Here is the data and screen shot...Also attached the pb file.

Syntax = 

LastTotal = CALCULATE(SUM(TableA[Total]),FILTER(TableA,TableA[Name]=EARLIER(TableA[Name])&&TableA[Date Receive]=EARLIER(TableA[Date Receive])-1))

Date ReceiveNameYearAmountTotalLastTotalProgramAContract No

1/2/2018	a	2018	150	150		150	1
1/3/2018	a	2018	150	300	150	150	1
1/4/2018	a	2018	150	450	300	150	1
2/1/2018	a	2018	150	1650		500	1
1/5/2018	a	2018	150	600	450	50	1
1/6/2018	a	2018	150	750	600	0	1
1/7/2018	a	2018	150	900	750	0	1
1/8/2018	a	2018	150	1050	900	0	1
1/9/2018	a	2018	150	1200	1050	0	1
1/10/2018	a	2018	150	1350	1200	0	1
1/11/2018	a	2018	150	1500	1350	0	1
1/2/2018	b	2018	150	150		150	2
1/3/2018	b	2018	150	300	150	150	2
1/4/2018	b	2018	150	450	300	150	2
1/5/2018	b	2018	150	600	450	50	2
1/6/2018	b	2018	150	750	600	0	2
1/7/2018	b	2018	150	900	750	0	2
1/8/2018	b	2018	150	1050	900	0	2

[Anonymous]

@v-cherch-msft one more thing. There are multiple reciecpts per day per the same contract. My apologies for not displaying that in the model. 

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may add the index in query editor.Please check the file.

Regards,

Cherie

[Anonymous]

@v-cherch-msft Worked perfectly! Huge thanks!!

[v-cherch-msft]

Hi @Anonymous

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

Regards,

Cherie