Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
AlB
Super User
Super User

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

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

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

Proud to be a Super User!




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

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

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.