Calculate quantity per month

[Fadian4]

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

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]

@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]

hi, thank you for your advice.

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