Forum Discussion

Arendp's avatar
Arendp
Helper 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(DateTable[Date])
    return CALCULATE(
        SUM('VoorraadMutaties (incl Voorraadniveau)'[fx.VoorraadBijAfAantal]),
        FILTER(
            ALLSELECTED(DateTable[Date]),
            ISONORAFTER(DateTable[Date], _currdate, DESC)
        )
    )

It gives me a nice visual like this:

However, I want the visual to show only the last 6 months of data.

 

When I do this, my visual changes to this:

 

Last value is -193, while it should be 302. This happens as it's starting to count from the starting point of the range of 26 weeks.

 

How can I adjust the measure so, that the visual is not affected by my filter? I know it's possible and have already looked for answers, but not managed to solve it. I know it's something like all() or allselected(), but I can't get it working.

 

Thanks in advance for your answer!

 

  • 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!

12 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    Can you use the ALLEXCEPT pattern so the cumulative mesure effects work from any starting point?

     

     FILTER(
            ALLEXCEPT(DateTable[Date]),
    • Arendp's avatar
      Arendp
      Helper III
      4 years ago

      so formula will be something like this? ->

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

      this gives me an error:

      A single value for the column 'Date' in the table 'DateTable' cannot be determined. This can happen if the measure's formula references a column that contains many values and does not specify a minimum, maximum, number, or sum aggregation that produces a single result.

       

      • Whitewater100's avatar
        Whitewater100
        Solution Sage
        4 years ago

        Hi:

        Sorry that didn't work. I was tthinking you could use the following two measures. You'll notice on the second one it has -180 (days) for going back six months. This part could be dynamic where you could make a small table of numbers say 1-12, representing the number of months to go back. I was wondering if this version works for your immediate question though. I bolded the flexible part.Thanks..

         

        Total = SUM('VoorraadMutaties (incl Voorraadniveau)'[fx.VoorraadBijAfAantal])

         

        RT 6M =

        var enddate = LASTDATE(DateTable[Date])

        var startdate = enddate - 180

        IF(ISBLANK([Total]), BLANK(),

        CALCULATE([Total], DATESBETWEEN(DateTable[Date], startdate, enddate)))

  • v-zhangti's avatar
    v-zhangti
    Community 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
      Helper 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
        Solution Sage
        4 years ago

        Hi:

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

        Thanks..