Hello everyone, I need a help regarding Query language in Power Bi

[Sai_Kumar]

I had an Open order sales column, Source column, Document date column and Document_due date column in my table(Source column and Document date column append from the other table)

"This was my Formula for Open Order Sale Column"
Open Order Sales = if [Source] = "Actuals" and [ObjType] = "17" and [LineStatus] = "O" then [LineTotal] else 0

The problem was - The Open order sales column is generating values based on Document date(But we need the open order sales to generate regarding Document_due date)

Is there any Function in the query language to tell the open order sales column to take the given column dates?

Thanks to everyone for participating, and I appreciate any help you guys can provide.

[V-pazhen-msft]

@Sai_Kumar 

I guess you don't need to append. If you just create a relationship between the Document_due date(Table1) and the Document date(Table2), you should able to use the Document date to filter the measure. The relationship will be CalendarTable <=> Table1 <=>Table2

For relationship problems, it can be complicated since we don't have your full model. If possible, you can create a sample pbix with only tables and some relevant columns and removing sensitive data.

Best Regards

Paul Zheng _ Community Support Team

[Sai_Kumar]

I tried the way as you said- But really, it doesn't affect anything(Same as before)

[Sai_Kumar]

Hi gdarakji,

Thanks for looking into the issue - Open Order Sales was a measure.

[gdarakji]

Do you have any date dimension tables related to your fact table? If so, then you need to relate the date table to the Document_due date and use the USERELATIONSHIP function to perform the calculation. If not, then could it be that you are adding the Document date instead of the Document_Due date to the visual?

[Sai_Kumar]

Yeah, I had a Date table that was Currently related to the Document date.

And, If I make the relationship inactive and If I make the relation active between Document Due Date and Date table then the Budget and Sales are not working (Open Order sales are working great). 

[gdarakji · Accepted solution]

I assume you are just adding the [DocDueDate] and the [Open order sales] fields to a visual and they are not working as expected. This makes perfect sense since the relationship between these 2 columns is not active. To make it work, you should keep the relationships as is but use a measure for the calculation. Assuming you need to calculate the sum of the [Open order sales], you can create and then use the below measure in your visuals:

[SumOpenOrderSales] = CALCULATE(SUM([Open order sales]),USERELATIONSHIP('MCS_POWERBI_FACT'[DocDueDate],'Master Calendar'[Date]))

[Sai_Kumar]

Yoo, That was right! - Thank you gdarakji. 
Really appreciated.

[gdarakji]

We need more details to be able to assist. Is the [Open Order Sales] field a column in a table or a measure? If it is a column, then please clarify how is it possible for the any date column to impact its value since neither [document date] nor [document_due date] are part of the equation.