Filter on visual impacting measure

[Arendp]

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!

[v-zhangti]

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]

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]

Hi:

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

Thanks..

[Arendp]

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]

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

[Whitewater100 · Accepted solution]

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. 

https://drive.google.com/file/d/1uNb3cBh4l89_EtPAZpjZayYAH2WbtNfJ/view?usp=sharing

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!

[Arendp]

Thanks for your help! I managed to set it up like you advised.

[Whitewater100]

Hi:

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

 FILTER(
            ALLEXCEPT(DateTable[Date]),

[Arendp]

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]

Hi:

If this doesn't work can you please provide sample data representing the issue? Thanks

[Whitewater100]

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)))

[Arendp]

Thanks for your answer! The problem with this is that my inventory doesn't show a value for each day. (so if there isn't a day with sales, it still needs to show the inventory value, while your measure doesn't show any value for that day).

Also the inventory value is not correct (a lot values below 0).

I was hoping I could just put a filter to only show the last 6 months, and that on the background the original inventory measure can keep the same...