Forum Discussion

bstark1287's avatar
bstark1287
Helper II
3 years ago
Solved

Order Book by month

I am trying to create an order book by month visual. Basically I need to show total order book by month.

So for January 2021 it would be any orders with the following criteria (assuming my 'OB Running Total'[Date] is my calendar table):
'Raw Order Data'[CREATE_DATE] >= 'OB Running Total'[Date], but also 'OB Running Total'[Date] <= 'Raw Order Data'[Order Invoiced Date], as well as 'OB Running Total'[Date] <= 'Raw Order Data'[Order Cancelled Date].

I have a working excel version but I am unable to get it working in Powerbi. I would like to create this using m-language if possible. Example of excel formula below.

=
SUMIFS('Raw Order Data'[ACT_MATERIAL_COST], 'Raw Order Data'[CREATE_DATE], "<=" ‘OB Running Total’[Date], 'Raw Order Data'[Order Invoiced Date], ">=" ‘OB Running Total’[Date])
+
SUMIFS('Raw Order Data'[ACT_MATERIAL_COST],'Raw Order Data'[CREATE_DATE],"<=" ‘OB Running Total’[Date],'Raw Order Data'[Order Invoiced Date],"=")
-
SUMIFS('Raw Order Data'[ACT_MATERIAL_COST], 'Raw Order Data'[Order Cancelled Date], "<=" ‘OB Running Total’[Date])

Need Help
Tutorial Requests
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, bstark1287 
    For this dax , [ACT_MATERIAL_COST] is a column right?

    In DAX, measures are aggregated values that are evaluated in the context of filtering, so we cannot use columns as evaluation formulas, we can use aggregate functions such as SUM() to achieve this. like this:

     

    TEST = CALCULATE(
    SUM([ACT_MATERIAL_COST]),
    'PSC Raw Order Data'[CREATE_DATE] >= 'tbl_Calendar'[Date]
    'PSC Raw Order Data'[Order Invoiced Date] <= 'tbl_Calendar'[Date],
    'PSC Raw Order Data'[Order Cancelled Date] = BLANK())

     

    For your need, I'm sorry, but I'm not very clear about your needs and your table structure and the results you ultimately want to output.

    Can you provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

3 Replies

  • bstark1287's avatar
    bstark1287
    Helper II
    3 years ago

    Something along these lines. Obviously I am terrible at DAX but I am trying to learn as quickly as possible. 

     

    TEST = CALCULATE (SUMX(FILTER('PSC Raw Order Data','PSC Raw Order Data'[CREATE_DATE] <= 'tbl_Calendar'[Date]), AND(FILTER('PSC Raw Order Data', 'PSC Raw Order Data'[Order Invoiced Date] >= 'tbl_Calendar'[Date])), AND(FILTER('PSC Raw Order Data', 'PSC Raw Order Data'[Order Cancelled Date] >= 'tbl_Calendar'[Date]))),'PSC Raw Order Data'[ACT_MATERIAL_COST])
  • bstark1287's avatar
    bstark1287
    Helper II
    3 years ago

    I tried this as well and it did not work:

    TEST = CALCULATE(
        [ACT_MATERIAL_COST],
        'PSC Raw Order Data'[CREATE_DATE] >= 'tbl_Calendar'[Date]
        'PSC Raw Order Data'[Order Invoiced Date] <= 'tbl_Calendar'[Date],
        'PSC Raw Order Data'[Order Cancelled Date] = BLANK())
    • v-yueyunzh-msft's avatar
      v-yueyunzh-msft
      Community Support
      3 years ago

      Hi, bstark1287 
      For this dax , [ACT_MATERIAL_COST] is a column right?

      In DAX, measures are aggregated values that are evaluated in the context of filtering, so we cannot use columns as evaluation formulas, we can use aggregate functions such as SUM() to achieve this. like this:

       

      TEST = CALCULATE(
    SUM([ACT_MATERIAL_COST]),
    'PSC Raw Order Data'[CREATE_DATE] >= 'tbl_Calendar'[Date]
    'PSC Raw Order Data'[Order Invoiced Date] <= 'tbl_Calendar'[Date],
    'PSC Raw Order Data'[Order Cancelled Date] = BLANK())

       

      For your need, I'm sorry, but I'm not very clear about your needs and your table structure and the results you ultimately want to output.

      Can you provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

       

      Best Regards,

      Aniya Zhang

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly