Forum Discussion

Arendp's avatar
Arendp
Icon for Helper III rankHelper III
4 years ago
Solved

Filter on visual impacting measure

hello, hope someone can help me with the following.   I have table with inventory development per day. To get inventory status per day, I use: Cumulatief Voorraad nieuw = var _currdate = max...
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    Hi:

    Please see attached file for example of cumulative total (page2).

    Cumulative Sales Example =
    var runningtotal = CALCULATE(SUM(Sales[Sales Amt]), FILTER(ALLSELECTED(Dates),
    Dates[Date] <= MAX(Dates[Date])))
    Return
    IF(CALCULATE(SUM(Sales[Sales Amt])) = BLANK(),BLANK(), runningtotal)
     
    I willattach a file as example,please see page 2. 
     
    If you want to get you inventory value as any point in time and you have various days when inventory is last reported you should generally have a Date Table, a product dimension table(if inventory is stored at this grain) and a Inventory Fact Table, with date,product,amount on hand.
     
    If you want to creat an example of these tables in excel (with expected results) I can try to 
    do a measure to show last inventory value.
     
    I hope this helps!
v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
4 years ago

Hi, Arendp 

 

In a case like yours, you can write a separate Measure to filter the last 6 months of views.

As an example.

Measure = IF(TODAY()-SELECTEDVALUE('Table'[Date])<=180,1,0)

Then put Measure into the Filter of the view and set it equal to 1.

This will have no effect on your original calculations.

 

Best Regards,

Community Support Team _Charlotte

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

Arendp's avatar
Arendp
Icon for Helper III rankHelper III
4 years ago

Thanks for your answer!

Unfortunately, my problem is still there:

 

Without the measure my stock is 77 on 28-Oct, and with the measure stock is -19 on the same date.

Somehow it starts counting from 0.

Is my inventory formula wrong?

 

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi:

    Can you provide sample data to represent the way your model and tables(the related ones) work?

    Thanks..

    • Arendp's avatar
      Arendp
      Icon for Helper III rankHelper III
      4 years ago

      This is the table holding the data:

       

      This table holds the following important data:
      -Article
      -Inventory Date (date on which a change in inventory was reported)
      -Amount
      -Amount to deduct or to add
      -Running inventory total

      The idea is to see on each day in the graph, the inventory level. However this table doesn't hold a row for each day, it only shows those days on which a change in inventory was reported.

      So for example, inventory is on April-1 -> 100
      On 3 April, 50 are added
      On 5 April, 20 are sold
      And on 8 April 25 are sold. 

      Inventory needs to be:
      1 April 100
      2 April 100
      3 April 150
      4 April 150
      5 April 130
      6 April 130
      7 April 130
      8 April 105
      9 April 105

      That happens with the earlier described formula:

      Cumulatief Voorraad nieuw = 
    var _currdate = max(DateTable[Date])
    return CALCULATE(
        SUM('VoorraadMutaties (incl Voorraadniveau)'[fx.VoorraadBijAfAantal]),
        FILTER(
            ALLSELECTED(DateTable[Date]),
            ISONORAFTER(DateTable[Date], _currdate, DESC)
        )
    )


      However this formula can't be filtered on last 6 months only, which I try to achieve.

      Right now what happens, when my starting point is 3 April (due to filter set on 3 April):

      3 April 50
      4 April 50
      5 April 30
      6 April 30
      7 April 30
      8 April 5
      9 April 5

      It doesn't consider the 100 that were add on 1 April.

       

      Hope it's clear what I try to achieve 🙂

      • Arendp's avatar
        Arendp
        Icon for Helper III rankHelper III
        4 years ago

        Anyone has any clue on this? Maybe the setup of my measure is wrong, or how can't I put filters on this measure?