Monthly inventory

[mikaro]

My fact table consists of transactions where every row is either a negative or positive transaction ( i.e. profit or loss) and other info related to them.

I'm trying to figure out what is considered best practise when reporting inventory on a monthly basis. The main problem that I'm facing is that if I select for instance whole march I only get the transactions from that month when in reality Im interested on the inventory status in that month (meaning that the calculations should start from the point where the inventory was in the end of february).

All help and tips are much appreciated!

EDIT:

More info on my reply

[tringuyenminh92]

Hi @mikaro,

• In Modeling tab -> choose New Table: Dates= Calendarauto()
• Mapping relationship between 2 tables your fact table and Dates table
• Create calculated measure to sum value of current month and the last date of previous month

Inv Monthly = CALCULATE(SUM(Transactions[Value]),FILTER(ALL(Dates), Dates[Date] <= max(Dates[Date])  &&  Dates[Date] >= max(Dates[Date]) - day(max(Dates[Date]))  &&  Dates[Date].[Year]=YEAR(MAX(Dates[Date])))) 

To understand more about this technique, you could refer time pattern topic . 

My sample data and pbix file. Too lazy to check with more data. Hope you share more sample data in file, so I could quickly test above solution.

[mikaro]

Thanks @tringuyenminh92 i did very similar solution to this but i ran into a new problem with the measure below:

cumulative qty = CALCULATE(
    SUM(
        Transactions[value]
        );
        filter(
            ALL(
                Dates[date]);Dates[date] <= * last date here *)
                )

For example if i type in Date(2016,3,31) to the  * last date here * I get the result that that looking for, but if I get that date from for intance from a slicer (season from datetable e.g. 03-16 or full date 31.3.2016) and try to insert that to the * last date here * it gives me wrong outcome. For date from slicer i've tried DATE- and DATEVALUE- functions and neither of them have worked. I get the value from slicer with this measure:

choice = 
    CONCATENATEX (
        VALUES ( Dates[Date] );
        Dates[Date];
    )

One example of the final (not working correctly atm) measure would then be:

cumulative qty = CALCULATE(
    SUM(
        Transaction[value]
        );
        filter(
            ALL(
                Dates[Date]);Dates[Date] <= DATE(YEAR([choice];MONTH([choice];DAY([choice])
                )

Ill try to further elaborate what i im trying to achieve with this simple example:

Date          value

1.1.2016    1000

2.1.2016   -500

1.2.2016    700

3.2.2016   -200

Now when i would select 3.2.2016 i should get 1000 and when i would select 2.1.2016 I should get 500. Hopefully this clarified this a bit 🙂

This logic works fine if I type in the date but every time I try to use a value that is from slicer I get wrong outcome. Am i missing something with DATE-functions or am I trying to do this the wrong way?

[tringuyenminh92]

Hi @mikaro,

The date field in slicer is from Dates table or from your fact table? Did you create calculated measure or column?

[mikaro]

Hi @tringuyenminh92!

Date field in slicer is from Dates table and all functions in my previous post are calculated measures. For instance if i pick 31.3.2016 from slicer my calculated measure "choice" equals to 31.3.2016. The only calculated column in my project atm is season that is located in the Dates table (31.3.2016 -> 03-16).

Thanks in advance for helping me out!

[tringuyenminh92]

Hi @mikaro,

Maybe there are something wrong in filter

            Dates[Date]);Dates[Date] <= DATE(YEAR([choice];MONTH([choice];DAY([choice])

 Could you please share pbix and sample data so i could analyze it? And i'm not sure that i understand your expectation in choosing specific date. Could you explain it again?

[mikaro]

Hi @tringuyenminh92,

Here are the files ppix and transactions.

I added some examples and text boxes that will hopefully explain and clarify this problem even more. If they dont help please let me know and i'll try to explain again.

Thanks!

[tringuyenminh92 · Accepted solution]

Hi @mikaro,

If you are finding cummulative value, it should be:

cumulative qty = CALCULATE(
    SUM(
        Transactions[value]
        ),
        filter(
            ALL( Dates[Date]),Dates[Date] <=MAX(Dates[Date]) && year(Dates[Date])= year(MAX(Dates[Date]))
                ))

and mapping relationship when using dates table

if it's not your expectation, hope you describe your expectation resuslt again so i could quickly figure out solution.