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

[langkio]

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

[AlB]

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

[langkio]

Thanks for your response! 

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.

[ryan_mayu · Accepted solution]

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

[langkio]

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

[ryan_mayu]

@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

2. Reading related books

3. Online resources (e.g. https://docs.microsoft.com/en-us/dax/data-analysis-expressions-dax-reference)

4. Watching related vedio tutorials.

Hope this is helpful. Thanks