cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
langkio
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 Solution Supplier
Solution Supplier

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

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

5 REPLIES 5
ryan_mayu Solution Supplier
Solution Supplier

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

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Highlighted
Super User III
Super User III

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

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

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 Solution Supplier
Solution Supplier

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

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors