Forum Discussion

Sai_Kumar's avatar
Sai_Kumar
Frequent Visitor
5 years ago

Hello everyone, I need an help Regarding Power Query/Dax in Power BI

Hi Guys, 
Morning!

I am working on Sales data and the data has Open Order Sales by Doc_date and Doc_due_date in the MCS_PowerBI_Fact table.

 

Requirement: I am looking for an Open Order Sales visual by Doc_due date. - Currently, I am getting open order sales by Doc_date

Measures I am using

 

The following measure was for currency conversion,

OLD_Open_Order_Sales = SWITCH(
'Currency Selection'[SelectCurrency],
"Local Currency",SUMX(MCS_PowerBI_Fact,MCS_PowerBI_Fact[OpenOrderSalesLocal]),
"Australian Dollar",SUMX(MCS_PowerBI_Fact,MCS_PowerBI_Fact[OpenOrderSalesAUD]))

And By the following measure I am trying to get open order sales by DocDueDate - But, still, it was extracting by Doc date -
OpenOrderSales = CALCULATE(SUM([OpenOrderSalesAUD]),USERELATIONSHIP('MCS_POWERBI_FACT'[DocDueDate],'Master Calendar'[Date]))
 
***I need a measure to get the open order sales by DocDueDate.*** and if possible, to include both OpenOrderSalesAUD and OpenOrderSalesLocal in a measure.

Coming to relationships between two tables(MCS_PowerBI_Fact[DocDate] and Master calendar table[Date])

 

And, If I change the relationship to Doc_Date to Doc_Due_Date then the sales figures are effecting(Which was sales figures are getting wrong)

Any help can be appreciated 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Try:

     

    OpenOrderSales = CALCULATE(SUM([OpenOrderSalesAUD]), DISTINCT('MCS_POWERBI_FACT'[DocDueDate]), USERELATIONSHIP('Master Calendar'[Date]))

     

    --Nate

     

     

    • Sai_Kumar's avatar
      Sai_Kumar
      Frequent Visitor
      5 years ago

      Hi Watkinnc,

      Thanks for considering!

      I tried the way as you said 

      The error follows-
      Too few arguments were passed to the USERELATIONSHIP function. The minimum argument count for the function is 2.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Sorry--copy/paste error:

     

    OpenOrderSales = CALCULATE(SUM([OpenOrderSalesAUD]), DISTINCT('MCS_POWERBI_FACT'[DocDueDate]), USERELATIONSHIP('MCS_POWERBI_FACT'[DocDueDate],'Master Calendar'[Date]))

    • Sai_Kumar's avatar
      Sai_Kumar
      Frequent Visitor
      5 years ago

      I tried it - But, the measure output was the same as my last measure as mentioned in the post.

      • Sai_Kumar's avatar
        Sai_Kumar
        Frequent Visitor
        5 years ago

        If you see the Doc_Date and Doc_Due_Date columns 
        The requirement was, we need to extract only Doc_Due_date if Doc_Due_date was in the Current month.

         

        The OLD_Open Order Sales measure was working but giving us all open order Sales - where we need only present and future months open order sales.