Calculating Direct Material from AX 2012 with DAX function

[DF8859]

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

[Anonymous]

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 · Accepted solution]

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 🙏