cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
langkio Frequent Visitor
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 
A1512/11/2018
B1412/11/2018
C1312/11/2018
D1212/11/2018
A2012/18/2018
B1612/18/2018
C1712/18/2018
D1812/18/2018
A1212/24/2018
B1112/24/2018
C1012/24/2018
D912/24/2018

 

Thanks,

Kino

1 ACCEPTED SOLUTION

Accepted Solutions
ryan_mayu Established Member
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)

 

c1.JPG

5 REPLIES 5
ryan_mayu Established Member
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)

 

c1.JPG

Highlighted
Super User
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

 

langkio Frequent Visitor
Frequent Visitor

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

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.

langkio Frequent Visitor
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

ryan_mayu Established Member
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

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