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, and consumption align with month from posting date. 

Month 0 means --> current month

Month -1 means --> previous month 

Month -2 means --> 2 month ago

etc

 

So, if we open the dashboard on September 2022 , system will show like these:

1. Month 0 --> total of  quantity with posting date = September 2022, each item wich have transaction type = sale, negative adjustment, and consumption

2. Month -1 -->total of  quantity with posting date = August 2022, each item wich have transaction type = sale, negative adjustment, and consumption

3. Month -2 -->total of  quantity with posting date = July 2022, each item wich have transaction type = sale, negative adjustment, and consumption

etc

 

I've write on DAX like this, but it doesn't work:

 

Month_-1 =
VAR Month_Now = MONTH(NOW())
return
CALCULATE(
SUM(ItemMaster_ILE_Purchase[Quantity]),
ItemMaster_ILE_Purchase[Entry_Type]="Sale"||ItemMaster_ILE_Purchase[Entry_Type]="Consumption"||ItemMaster_ILE_Purchase[Entry_Type]="Negative Adjmt.",
Month(ItemMaster_ILE_Purchase[Posting_Date])=Month_Now-1
ALLEXCEPT(ItemMaster_ILE_Purchase,ItemMaster_ILE_Purchase[number])

)

 

The result always be like this. The value always blank eventhough I've already write Month(Now())+4 to get January 2023 value

 

 

*We didn't use filter date , because we want this dashboard automatically calculated each month without filtering

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

3 Replies

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

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    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.