Sum dollar value by open orders

[bstark1287]

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

[jdbuchanan71 · Accepted solution]

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

[bstark1287]

@jdbuchanan71 You are the freaking BEST! I slightly adjusted it to this and it worked flawlessly. 

[jdbuchanan71]

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]

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

[jdbuchanan71]

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

[jdbuchanan71]

Change the formatting of the measure.

[bstark1287]

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

[jdbuchanan71]

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

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