Forum Discussion

Fadian4's avatar
Fadian4
Regular Visitor
4 years ago
Solved

Calculate quantity per month

Hi, anyone please help me.    I want to create report like this.   I want to calculate quantity consumption each month and per item which have transaction type = sale, negative adjustment, ...
  • v-yadongf-msft's avatar
    v-yadongf-msft
    4 years ago

    Hi Fadian4 ,

     

    This is my test table:

     

    Please try following DAX:

    Month 0 = 
CALCULATE(
    SUM('Table'[Quanity]),
    FILTER('Table',
    'Table'[Entry_Type] IN{ "Sale" ,
     "Negative Adjmt" ,
    "Consumption"}
    && EOMONTH('Table'[Date],0)=EOMONTH(TODAY(),0)
    )
)

Month -1 = 
CALCULATE(
    SUM('Table'[Quanity]),
    FILTER('Table',
    'Table'[Entry_Type] IN{ "Sale" ,
     "Negative Adjmt" ,
    "Consumption"}
    && EOMONTH('Table'[Date],0)=EOMONTH(TODAY(),-1)
    )
)

Month -2 = 
CALCULATE(
    SUM('Table'[Quanity]),
   FILTER('Table',
    'Table'[Entry_Type] IN{ "Sale" ,
     "Negative Adjmt" ,
    "Consumption"}
    && EOMONTH('Table'[Date],0)=EOMONTH(TODAY(),-2)
    )
)

     

    You will get the result you want:

    Best regards,

    Yadong Fang

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Fadian4 , Create a new column in your date table

 

Month No  = "Month" & format(-1* datediff(eomonth(today(),0), eomonth([Date],0), Month) , "00")

 

and use that in visual

Fadian4's avatar
Fadian4
Regular Visitor
4 years ago

hi, thank you for your advice.

but still doesn't work on me. the output just "Month 00"