Need some help with DAX

[Aron_Moore]

Hello experts! Need help to create a measure.

I have a table of transactions like so:

The user wants to see each week the payment could land, from ship to due date like so:

I created a date table and joined the two tables so they can pivot on week or customer, whatever the user likes.

I've tried a few measures but I can't get the value to show at each week/period. Either it puts it all all periods or only the one.

Thank you!

[Aron_Moore · Accepted solution]

Think this is what I was looking for. Power BI Community

Initial results look promising. Will validate the data.

[v-huizhn-msft]

Hi @Aron_Moore,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Thanks,
Angelia

[Aron_Moore]

Still waiting to hear back from the testers.

[v-huizhn-msft]

Hi @Aron_Moore,

Got it. You post the link, I thought you found the solution. You'd better post the sample table and list the expected result if you want to get dedicated support.

Best Regards,
Angelia

[v-huizhn-msft]

Hi @Aron_Moore,

Please try the solution in the thread you found. And feel free to ask if you have any other issue.

Best Regards,
Angelia

[parry2k]

if without measure you put 

bill to cust on rows

date in column

sales in values (choose sum from drop down)

it will give you what you need. 

[Aron_Moore]

Maybe but I think it would take some redesign. Currently there is only one value per date in the model. They want to see the value across every date/bucket that is possible.

[parry2k]

that is ok, choose matrix visual and use fields as i mentioned and it should work.

[Aron_Moore]

I have tried that. Here are a couple matix using different date fields. It only populates the one date. Also, the user will be consuming the model via Excel/Publisher.

[parry2k]

what will be the logic to pick date 1 over date 2

you can create a calculated column based on that logic and then use that new calculated column in matrix. If you can provide more details, it will be helpful.

[Aron_Moore]

The user would pick date 1 or 2 depending on what they want to analyze. Maybe they want to see everything shipped on sale day or something. I could hide those dates if we need to.

For the specific reporting request they want to see the total amount of payments that could come in each week.

I tried putting it in plain English to then convert to DAX. Something like: For each 'Week Of' Sum 'Amount' where dates between 'Ship' and 'Invoice Due' in 'Week Of'.