how can i get last Day of the month Inventory quantity in a table

[shabirAhmad]

Dear Experts,

I have requirement, i want to get last day of the month inventory quantity(Closing Inventory Quantity) of the table. Could you please support me.

Best Regards,

Shabir Ahmad

[Royel]

@shabirAhmad, 
I am considering in your dataset, you have Date, Inventory columns. You want only the last day of each months inventory value.  Here is the dax, 

Last DayInventory = 
VAR MaxDate = MAX(Inventory[Date])
VAR LastDayOfMonth = EOMONTH(MaxDate, 0)
VAR LastDayInventoryQty = 
    CALCULATE(
        SUM(Inventory[Inventory]),
        FILTER(
           Inventory,
            Inventory[Date] = LastDayOfMonth
        )
    )
RETURN LastDayInventoryQty

And the result: 

Let me know @me the progress. 

Regards
Royel

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

---

[shabirAhmad]

Hi @Royel,

Can you please tell me about the above of your DAX.  In my Table there is only 1 DatePhsical that is the date the bases of this date i will find out inventory quantity and 2 value is Qty. Can you please add these 2 columns name in your DAX 1 DatePhysical and 2 Qty and my table name is INVENTORYTrans. Actually , i am new and not able to get your DAX and tried but no luck.

Best Regards,

Shabir Ahmad

[Royel · Accepted solution]

 , The name of the table is "INVENTORYTrans", where we have two columns (DatePhsical, Qty). Now, we will create a measure using these columns to get the last day of each month's inventory quantity.

Last DayInventory = 
VAR MaxDate = MAX(INVENTORYTrans[DatePhsical])
VAR LastDayOfMonth = EOMONTH(MaxDate, 0)
VAR LastDayInventoryQty = 
    CALCULATE(
        SUM(INVENTORYTrans[Qty]),
        FILTER(
           INVENTORYTrans,
            INVENTORYTrans[DatePhsical] = LastDayOfMonth
        )
    )
RETURN LastDayInventoryQty

Let me know if it is going to work or not. If not work, give me a short screenshot or your table with header and expected output in a sample Excel table will be very helpful. 

Thanks 
Royel

@shabirAhmad

[shabirAhmad]

BegininngInventory = VAR MaxDate = MAx(UEINVENTORYTURNSANDINVENTORYDAYS[DATEPHYSICAL]) VAR LastDayOfMonth = EOMONTH(MaxDate,0) VAR LastDayInventoryQty = CALCULATE(SUM(UEINVENTORYTURNSANDINVENTORYDAYS[DATEPHYSICAL]),FILTER(UEINVENTORYTURNSANDINVENTORYDAYS,UEINVENTORYTURNSANDINVENTORYDAYS[DATEPHYSICAL]= LastDayOfMonth)) return LastDayInventoryQty.

I used this DAX followed your suggestion but no result.  As per your suggestion above i want to clear my confusion that is, what is "LastDayInventoryQty"? Is it a field in my Table(Datasouce table) means it will be existing field physically in my table.

Best Regards,

Shabir Ahmad

[shabirAhmad]

Hi @Royel ,

Thanks for your reply, how i will add this column to my table(DataSource) LastDayInventoryQty

You declare it in the DAX formula but one thing i am comfused that this will be a physical column in my table (Datasource)?? Because i don't have this kind of field or column

Best Regards,

Shabir Ahmad