Forum Discussion
Calculating Direct Material from AX 2012 with DAX function
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 🙏
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.
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 🙏