cancel
Showing results for
Did you mean:
Frequent Visitor

## Total change from previous available date with non-continuous date in data

Hi,

I have a set of data with non-continuous dates and inventory level.

How do I create a formula that gives me the change in total inventory when I select a date from the slicer? For example, if I select the date in the slicer as 12/18/18, the change in inventory should be 17 ( 71 total inventory on 12/18 minus 54 total inventory on 12/11).

 Facility Inventory Date A 15 12/11/2018 B 14 12/11/2018 C 13 12/11/2018 D 12 12/11/2018 A 20 12/18/2018 B 16 12/18/2018 C 17 12/18/2018 D 18 12/18/2018 A 12 12/24/2018 B 11 12/24/2018 C 10 12/24/2018 D 9 12/24/2018

Thanks,

Kino

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Total change from previous available date with non-continuous date in data

@langkio

Maybe you can try this coding.Hope this is helpful.Thanks.

```Inventory gap =
VAR D=SELECTEDVALUE('Sheet2'[DATE])
VAR maxdate=MAXX(FILTER(ALL('Sheet2'),'Sheet2'[DATE]<D),'Sheet2'[DATE])
return
SUM(Sheet2[INVENTORY])-CALCULATE(sum(Sheet2[INVENTORY]),'Sheet2'[DATE]=maxdate)```

5 REPLIES 5
Established Member

## Re: Total change from previous available date with non-continuous date in data

@langkio

Maybe you can try this coding.Hope this is helpful.Thanks.

```Inventory gap =
VAR D=SELECTEDVALUE('Sheet2'[DATE])
VAR maxdate=MAXX(FILTER(ALL('Sheet2'),'Sheet2'[DATE]<D),'Sheet2'[DATE])
return
SUM(Sheet2[INVENTORY])-CALCULATE(sum(Sheet2[INVENTORY]),'Sheet2'[DATE]=maxdate)```

Highlighted
Super User

## Re: Total change from previous available date with non-continuous date in data

Hi @langkio

Try this measure for instance in a Card visual. It assumes you select single dates in the slicer.

```InventoryChange =
VAR _SelectedDateInventory =
SUM ( Table1[Inventory] )
VAR _PreviousDate =
CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < MAX ( Table1[Date] ) )
VAR _PreviousInventory =
CALCULATE ( SUM ( Table1[Inventory] ), Table1[Date] = _PreviousDate )
RETURN
_SelectedDateInventory - _PreviousInventory```

Frequent Visitor

## Re: Total change from previous available date with non-continuous date in data

Though for some reason I received this: A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Frequent Visitor

## Re: Total change from previous available date with non-continuous date in data

Hello Ryan,

Thank you for your prompt response. Your code worked perfectly when I tried it!

Really appreciate your help. Was wondering if you would have any recommended resourses for learning DAX?

All the best,

Langkio

Established Member

## Re: Total change from previous available date with non-continuous date in data

@langkio

I don't have a lot of experience that can share with you because I started to learn DAX about 3 months ago.

Below are my approaches to learn this:

1. Powerbi Community