Forum Discussion

mikaro's avatar
mikaro
Frequent Visitor
9 years ago
Solved

Monthly inventory

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

 

 

Need Help
  • tringuyenminh92's avatar
    tringuyenminh92
    9 years ago

    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.

7 Replies

  • tringuyenminh92's avatar
    tringuyenminh92
    Memorable Member
    9 years ago

    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's avatar
      mikaro
      Frequent Visitor
      9 years ago

      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's avatar
        tringuyenminh92
        Memorable Member
        9 years ago

        Hi mikaro,

         

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