Forum Discussion

DF8859's avatar
DF8859
Frequent Visitor
3 years ago
Solved

Calculating Direct Material from AX 2012 with DAX function

Hi, Pretty new to Power BI and DAX but I'm having problem calculating a DAX formula getting some information from Microsoft AX 2012. Trying to get the the SUM of all components with certain costgr...
DAX Filtering
DAX Formulas
  • DF8859's avatar
    DF8859
    3 years ago

    Hi,

    Managed to get a bit further yesterday.

    Greated a new table called DM where I filtered posts within a period and categories.
    -------------------------------------------------------------------------

    DM = CALCULATETABLE(BOMCALCTRANS,DATESBETWEEN(BOMCALCTRANS[TRANSDATE],"2023-01-01", "2023-01-31"),BOMCALCTRANS[COSTGROUPID]="SF" || BOMCALCTRANS[COSTGROUPID]="Mtrl" || BOMCALCTRANS[COSTGROUPID]="FP")
    ----------------------------------------------------------------------------------------------------------


    Then I created a new Column, DM, in that new table and also created a relationship with another table.
    This new table sums all Material and SF products by the PRICECALCID.
    -------------------------------------------------------------------------------------------------------
    DM = CALCULATE(SUM(DM[SALESPRICEQTY]),ALLEXCEPT(DM,DM[PRICECALCID]), BOMCALCTRANS[COSTGROUPID]="Mtrl" || BOMCALCTRANS[COSTGROUPID]="SF")
    -------------------------------------------------------------------------------------------------------

    For now the limitation is the static dates that I set when I create the new table. I would like
    to med able to choose the dates from the date slicer.
    My next thing to work on is to create a Matrix with three DM values for the Q1 quarter and show sales for each month.

    Thank you very much for your support Anonymous 🙏
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi DF8859 ,

Please have a try.

Create a measure.

DM =
VAR _DMLastdate =
    MAXX ( ALLSELECTED ( BOMCALATRANS ), BOMCALATRANS[TRANSDATE] )
RETURN
    CALCULATE (
        SUM ( BOMCALCTRANS[SALESPRICEQTY] ),
        FILTER (
            ALL ( BOMCALCTRANS ),
            ( BOMCALCTRANS[COSTGROUPID] = "SF"
                || BOMCALCTRANS[COSTGROUPID] = "Mtrl" )
                && BOMCALATRANS[TRANSDATE] = _DMLastdate
                && BOMCALCTRANS[PRICECALCID] = SELECTEDVALUE ( BOMCALCTRANS[PRICECALCID] )
        )
    )

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

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

 

DF8859's avatar
DF8859
Frequent Visitor
3 years ago

Hi,

Managed to get a bit further yesterday.

Greated a new table called DM where I filtered posts within a period and categories.
-------------------------------------------------------------------------

DM = CALCULATETABLE(BOMCALCTRANS,DATESBETWEEN(BOMCALCTRANS[TRANSDATE],"2023-01-01", "2023-01-31"),BOMCALCTRANS[COSTGROUPID]="SF" || BOMCALCTRANS[COSTGROUPID]="Mtrl" || BOMCALCTRANS[COSTGROUPID]="FP")
----------------------------------------------------------------------------------------------------------


Then I created a new Column, DM, in that new table and also created a relationship with another table.
This new table sums all Material and SF products by the PRICECALCID.
-------------------------------------------------------------------------------------------------------
DM = CALCULATE(SUM(DM[SALESPRICEQTY]),ALLEXCEPT(DM,DM[PRICECALCID]), BOMCALCTRANS[COSTGROUPID]="Mtrl" || BOMCALCTRANS[COSTGROUPID]="SF")
-------------------------------------------------------------------------------------------------------

For now the limitation is the static dates that I set when I create the new table. I would like
to med able to choose the dates from the date slicer.
My next thing to work on is to create a Matrix with three DM values for the Q1 quarter and show sales for each month.

Thank you very much for your support Anonymous 🙏