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 costgroups to present as a column in this Power BI table, which is the sales a period selected with a slicer.

 

This SQL does the job when I have found the right "PriceCalcID":
Select SUM(SALESPRICEQTY) from BOMCALCTRANS where PRICECALCID like '058256' and ( COSTGROUPID like 'Mtrl' OR COSTGROUPID like 'SF' )

Trying to this but know I'm missing something. I need first to find the latest date in the selected period.
And for that certain date I need to find the PriceCalcID for the Product No in the above Power BI table.

DM =
VAR DMLastdate = LASTDATE(BOMCALCTRANS[TRANSDATE])            // Find the latest price change date
VAR DMPriceCalcID = SELECTEDVALUE(BOMCALCTRANS[PRICECALCID])   // Find the specific ID for the latest pricechange
VAR DMSumPriceSales =
    CALCULATE(SUM(BOMCALCTRANS[SALESPRICEQTY]),
        FILTER(BOMCALCTRANS, BOMCALCTRANS[COSTGROUPID]="SF" || BOMCALCTRANS[COSTGROUPID] = "Mtrl")
)
Return DMSumPriceSales

Thank you very much in advance 🙏

Daniel


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 🙏

2 Replies

  • 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 🙏