Forum Discussion

Sai_Kumar's avatar
Sai_Kumar
Frequent Visitor
5 years ago
Solved

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

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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]))

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • Sai_Kumar's avatar
    Sai_Kumar
    Frequent Visitor
    5 years ago

    Hi gdarakji,

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


    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Sai_Kumar
        Frequent Visitor
        5 years ago

        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). 

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Sai_Kumar
      Frequent Visitor
      5 years ago

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