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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
temuulenchoi
Frequent Visitor

Cumulative total between date slicer

Hi there,

I am trying to filter a list of cumulative Inventory with a date slicer.
It works when I move the "until" side, but when I move the "from" side, it shows wrong numbers. 

Example "Sale" table which is used for calculating:

ItemInQtyOutQtyDate
A2022022/07/14
A 52022/07/15
A 42022/07/16
A 82022/07/17
A1532022/07/18
A 52022/07/19
A 22022/07/20
A1062022/07/21

I also have a Date Table which is used for the slicer and has relationship to the Sales table. 


Following measure is used:

Cumulative Total Inventory=

CALCULATE(
    SUM(Sale[InQty])-SUM(Sale[OutQty]),
    FILTER(ALLSELECTED(DateTable[Date]),
    DateTable[Date] <= max(DateTable[Date]) && max(DateTable[Date]) <= TODAY()))

The resulting table would be 
Cumulative Total inventoryDate
182022/07/14
132022/07/15
92022/07/16
12022/07/17
132022/07/18
82022/07/19
62022/07/20
102022/07/21


And the desired table, for example, between 2022/07/19 and 2022/07/21, would be:
Cumulative Total inventoryDate
82022/07/19
62022/07/20
102022/07/21


Thank you very much in advance!
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@temuulenchoi , You should use all as inventory need to build from start

 

Cumulative Total Inventory=
CALCULATE(
SUM(Sale[InQty])-SUM(Sale[OutQty]),
FILTER(ALL(DateTable[Date]),
DateTable[Date] <= max(DateTable[Date]) && (DateTable[Date]) <= TODAY()))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

6 REPLIES 6
ryan_mayu
Super User
Super User

@temuulenchoi 

what's the logic of calculating the inventory? It's because what i calculate is different from yours. Could you pls explain more on this?

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I'm sorry, there must have been an error on copying the table. I just edited the post with the correct table. Thanks!

 

@temuulenchoi 

pls see the attachment and check if this is what you want.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@temuulenchoi , You should use all as inventory need to build from start

 

Cumulative Total Inventory=
CALCULATE(
SUM(Sale[InQty])-SUM(Sale[OutQty]),
FILTER(ALL(DateTable[Date]),
DateTable[Date] <= max(DateTable[Date]) && (DateTable[Date]) <= TODAY()))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak, now I'm trying to count the out of stock dates. Imagine if the inventory was 0 between 2022/07/25 and 2022/07/28, the following measure returns 4. 

Days out of stock =
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            CROSSJOIN( VALUES(DateTable[Date]), VALUES(Sale[Item])),
"Inventory Levels", [Cumulative Total Inventory]),
[Inventory Levels] = 0  && NOT(ISBLANK([Inventory Levels]))))

but if i start the slicer from 2022/07/26 for example, it returns blank. Could you help me with this? Thank you very much in advance!

Thanks! This worked very well

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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