Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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)
Proud to be a 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
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.
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)
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
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
Proud to be a Super User!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |