Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Running 12 month Total with Condition

Hi,   I want to create a measure to calculate "Total shipment count for running 12 months". But the condition is if there are multiple shipment(i.e. >1) in same Month&Year(eg. for December 2017 the...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Anonymous

    As tested, when i add a measure in the SUMX function, it shows the same value as yours.

    Measure 3 = SUMX(DISTINCT(Table2),[Measure])

     

    Here is my pbix

     

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Anonymous

Does the "'Table_BulkMOQ'[Total Shipment Count]" is a column which shows 1 for each row?

Then this column is added to the table and set "sum" instead of "don't summarize".

 

You could create a column use "IF" to judge if here are multiple shipment(i.e. >1) in same Month&Year.

Total Shipment Count2=IF('Table_BulkMOQ'[Total Shipment Count]>1,1,'Table_BulkMOQ'[Total Shipment Count])

Then replace 'Table_BulkMOQ'[Total Shipment Count] with 'Table_BulkMOQ'[Total Shipment Count2] in R12 count of shipment.

 

Best Regards

Maggie

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi v-juanli-msft

    Yes, "Total Shipment Count" is calculated column which displays 0 & 1 that is used for further calculations.

    I used it in a Table with "Sum" function but here the thing is i want to perform "Running 12 Months Shipment count" & if there are multiple shipment in Same month and Year While counting 12 months Shipment for particular Shipto-Material then it should be counted as 1.  

    Can it be done using measure or do i have to create calculated table/columns?

    I used following DAX to calculate total Shipment: 

    Total Shipment Count     = VAR currentIndex=Table_BulkMOQ[NewIndex]
    var previousShipment= CALCULATE(FIRSTNONBLANK(Table_BulkMOQ[Plant_Ship-to_Shipment_Material],TRUE()),
    FILTER(Table_BulkMOQ,Table_BulkMOQ[NewIndex]=currentIndex-1))
    var sameShipment=Table_BulkMOQ[Plant_Ship-to_Shipment_Material]=previousShipment
    Return
    IF(Table_BulkMOQ[Plant_Ship-to_Shpmt_Mat Gross KGS]<>0,IF(sameShipment,0,1),0)