Forum Discussion

bstark1287's avatar
bstark1287
Helper II
3 years ago
Solved

Sum dollar value by open orders

I need help with some DAX. I have a working DAX that counts open orders. I am wanting to adjust this DAX to calculate total sales for open orders. I am not sure why it won't calculate. Here is the working version:

 

Open Tickets =
VAR tmpTickets = ADDCOLUMNS('OB Raw Order Data',"Effective Date",IF(ISBLANK([Order Closed Date]),TODAY(),[Order Closed Date]))
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpTickets,
'tbl_Calendar'
),
AND(
([Date] >= [CREATE_DATE]) && ([Date] <= [Effective Date]),
OR(([Order Closed Date] >= [End Of Month]), ISBLANK([Order Closed Date]))
)
),
"ID",[Order/Line],
"Date",[Date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))
RETURN COUNTROWS(tmpTable1)
 
 
Here is my attempt to get it to sum sales dollars:
OB USD =
VAR tmpdollars = ADDCOLUMNS('OB Raw Order Data',"Effective Date",IF(ISBLANK([Order Closed Date]),TODAY(),[Order Closed Date]))
VAR tmpTable2 =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpdollars,
'tbl_Calendar'
),
AND(
([Date] >= [CREATE_DATE]) && ([Date] <= [Effective Date]),
OR(([Order Closed Date] >= [End Of Month]), ISBLANK([Order Closed Date]))
)
),
"ID",[ACT_MATERIAL_COST],
"Date",[Date]
)
VAR tmpTable3 = GROUPBY(tmpTable2,[ID],"Sum",SUMX(CURRENTGROUP(),[Date]))
RETURN SUM(tmpTable3)Working Count of open orderNot working attempt to sum sales dollars
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    3 years ago

    bstark1287 

    Reading the code, it looks like you are picking the date and wanting to sum the 'OB Raw Order Data'[ACT_MATERIAL_COST] where the 'OB Raw Order Data'[CREATE_DATE] is on or before the date and the 'OB Raw Order Data'[Order Closed Date] is > the 'tbl_Calendar'[End Of Month] or it is blank.  

    Give this measure a try and see if it is what you are looking for.

    OB USB =
VAR _Date = MIN ( 'tbl_Calendar'[Date] )
VAR _EoM = MIN ( 'tbl_Calendar'[End Of Month] )
RETURN
    CALCULATE (
        SUM ( 'OB Raw Order Data'[ACT_MATERIAL_COST] ),
        'OB Raw Order Data'[CREATE_DATE] <= _Date,
        ( 'OB Raw Order Data'[Order Closed Date] > _EoM || ISBLANK ( 'OB Raw Order Data'[Order Closed Date] ) )
    )

     

9 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    bstark1287 

    Reading the code, it looks like you are picking the date and wanting to sum the 'OB Raw Order Data'[ACT_MATERIAL_COST] where the 'OB Raw Order Data'[CREATE_DATE] is on or before the date and the 'OB Raw Order Data'[Order Closed Date] is > the 'tbl_Calendar'[End Of Month] or it is blank.  

    Give this measure a try and see if it is what you are looking for.

    OB USB =
VAR _Date = MIN ( 'tbl_Calendar'[Date] )
VAR _EoM = MIN ( 'tbl_Calendar'[End Of Month] )
RETURN
    CALCULATE (
        SUM ( 'OB Raw Order Data'[ACT_MATERIAL_COST] ),
        'OB Raw Order Data'[CREATE_DATE] <= _Date,
        ( 'OB Raw Order Data'[Order Closed Date] > _EoM || ISBLANK ( 'OB Raw Order Data'[Order Closed Date] ) )
    )

     

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    bstark1287 

    Try it with a SUMX over the tmpTable3

    OB USD =
VAR tmpdollars =
    ADDCOLUMNS (
        'OB Raw Order Data',
        "Effective Date", IF ( ISBLANK ( [Order Closed Date] ), TODAY (), [Order Closed Date] )
    )
VAR tmpTable2 =
    SELECTCOLUMNS (
        FILTER (
            GENERATE ( tmpdollars, 'tbl_Calendar' ),
            AND (
                ( [Date] >= [CREATE_DATE] )
                    && ( [Date] <= [Effective Date] ),
                OR (
                    ( [Order Closed Date] >= [End Of Month] ),
                    ISBLANK ( [Order Closed Date] )
                )
            )
        ),
        "ID", [ACT_MATERIAL_COST],
        "Date", [Date]
    )
VAR tmpTable3 =
    GROUPBY ( tmpTable2, [ID], "@Sum", SUMX ( CURRENTGROUP (), [Date] ) )
RETURN
    SUMX ( tmpTable3, [@Sum] )
    • bstark1287's avatar
      bstark1287
      Helper II
      3 years ago

      It is formatting it as a date now. Not sure why, the working DAX for open tickets formatted it as a whole number. jdbuchanan71 

    • bstark1287's avatar
      bstark1287
      Helper II
      3 years ago

      Maybe I am missing something but it will not allow me to change the format

       

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    Oh, you are telling it to look at the [Date] field in the earlier step.

     

     

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    You need to change the field from [Date] to the whatever the field is that has the amounts you are trying to add up.

    • bstark1287's avatar
      bstark1287
      Helper II
      3 years ago

      jdbuchanan71 Thank you, that is getting me one step closer! I have made these changes, but it seems my SUMX either in my Var or in my Return are causing the totals to calculate incorrectly. Any thoughts on how to fix?

      OB USB =
      VAR tmpdollars = ADDCOLUMNS('OB Raw Order Data',"Effective Date",IF(ISBLANK([Order Closed Date]),TODAY(),[Order Closed Date]))
      VAR tmpTable2 =
      SELECTCOLUMNS(
          FILTER(
              GENERATE(
                  tmpdollars,
              'tbl_Calendar'
              ),
              AND(  
                  ([Date] >= [CREATE_DATE]) && ([Date] <= [Effective Date]),
                  OR(([Order Closed Date] >= [End Of Month]), ISBLANK([Order Closed Date]))
              )
          ),
          "ID",[Order/Line],
          "Date",[Date],
          "Mtl",[ACT_MATERIAL_COST]
      )
      VAR tmpTable3 = GROUPBY(tmpTable2,[ID],"@Sum",SUMX(CURRENTGROUP(),[Mtl]))
      RETURN SUMX(tmpTable3,[@Sum])