Solved: Re: Dax for calculating how much item is left

One-Dash · ‎03-10-2023

Hello! This might be a bit complicated, but i will try to explain as best i can. i have two tables

Date - a table for calendaryear

Invoice - a table which includes list of items with different columns to track their progress to completion. Column includes

Ticket#, DateReceived, DateStarted, DateSentQA, DatePosted.

I want to track how many tickets are left at the end of each day. I used to do this manually in excel by doing this calculation below

we have a known a starting point for the # of tickets on 1/1/2022 which is 700, what makes this tricky is we have new tickets being added each day

Date	start	New Requests	Sent to QA	Posted	End
1/1/2022	700	3	1	1	702
1/2/2022	702	6	5	4	704
1/3/2022	704	1	7	6	699

on the table

start = #ticket at start of the day

New request column = #tickets with DateReceived same as Date value

Sent to Qa = #tickets with DateSentQA same as Date value

Posted = #tickets with DatePosted same as Date value

End = Remaining ticket at end of the day (Remaining Start+New Request - Posted)

Using the end column from the table i make the chart below.

is there a way to do this in PBI where the End column values can be auto calculated with out having to use excel ? thank you

Anonymous · ‎03-15-2023

Hi ,

Please have a try.

New request column =
CALCULATE (
    COUNT ( 'Table'[DATE RECEIVED] ),
    FILTER ( 'Table', 'Table'[DATE RECEIVED] = EARLIER ( 'date'[Date] ) )
)

Please cancel the relationship between the tables.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous · ‎03-14-2023

Hi @One-Dash ,

I have created a new sample, please refer to my pbix file to see if it helps you.

Measure =
VAR _a =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[date],
        "result", MAX ( 'Table'[new request] ) - MAX ( 'Table'[Posted] )
    )
RETURN
    700
        + SUMX ( FILTER ( _a, [date] <= MAX ( 'Table'[date] ) ), [result] )

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

One-Dash · ‎03-15-2023

Invoice Table sample

DATE RECEIVED	Date Started	Date Sent to QA	DATE Posted	TIME WORKED
31-Jan-23
31-Jan-23
30-Jan-23
30-Jan-23
30-Jan-23
30-Jan-23
27-Jan-23
27-Jan-23
27-Jan-23
27-Jan-23
27-Jan-23
27-Jan-23
27-Jan-23
27-Jan-23
27-Jan-23
27-Jan-23
27-Jan-23
26-Jan-23
25-Jan-23
25-Jan-23
25-Jan-23
25-Jan-23
25-Jan-23
25-Jan-23
25-Jan-23
25-Jan-23
25-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
23-Jan-23
19-Jan-23
19-Jan-23
19-Jan-23
19-Jan-23
19-Jan-23
19-Jan-23
19-Jan-23
19-Jan-23
19-Jan-23
19-Jan-23
18-Jan-23
18-Jan-23
18-Jan-23
17-Jan-23
17-Jan-23
17-Jan-23
17-Jan-23
17-Jan-23
17-Jan-23
17-Jan-23
17-Jan-23
17-Jan-23
17-Jan-23
17-Jan-23
13-Jan-23
13-Jan-23
13-Jan-23
13-Jan-23
13-Jan-23
13-Jan-23
13-Jan-23
13-Jan-23
13-Jan-23
12-Jan-23
12-Jan-23
12-Jan-23
12-Jan-23
12-Jan-23
12-Jan-23
12-Jan-23
12-Jan-23
11-Jan-23
11-Jan-23
11-Jan-23
11-Jan-23
11-Jan-23
11-Jan-23
11-Jan-23
11-Jan-23
11-Jan-23
11-Jan-23
11-Jan-23
11-Jan-23
10-Jan-23
10-Jan-23
10-Jan-23
10-Jan-23
10-Jan-23
10-Jan-23
10-Jan-23
9-Jan-23
9-Jan-23
9-Jan-23
9-Jan-23
9-Jan-23
9-Jan-23
9-Jan-23
9-Jan-23
6-Jan-23
6-Jan-23
6-Jan-23
6-Jan-23
6-Jan-23
6-Jan-23
6-Jan-23
5-Jan-23
5-Jan-23
4-Jan-23
3-Jan-23
3-Jan-23
3-Jan-23
3-Jan-23
3-Jan-23
3-Jan-23
3-Jan-23
3-Jan-23
3-Jan-23
29-Dec-22
29-Dec-22
29-Dec-22
29-Dec-22
28-Dec-22
22-Dec-22
7-Apr-22
3-Mar-22
3-Jan-23
16-Dec-22
15-Dec-22
28-Nov-22
14-Nov-22
17-Oct-22
3-Oct-22
3-Oct-22
21-Sep-22
21-Sep-22
13-Sep-22
30-Aug-22
14-Jul-22
10-May-22
7-Apr-22
3-Mar-22
3-Mar-22
8-Feb-22
11-Apr-22				180
4-Jan-23	13-Mar-23
3-Jan-23	10-Mar-23
29-Dec-22	9-Mar-23
28-Dec-22	14-Mar-23
19-Dec-22	13-Mar-23
14-Dec-22	6-Mar-23
14-Dec-22	13-Mar-23
14-Dec-22	10-Mar-23
8-Dec-22	1-Mar-23
11-Nov-22	1-Feb-23
9-Nov-22	16-Feb-23
4-Nov-22	6-Feb-23
24-Oct-22	14-Dec-22
17-Oct-22	6-Dec-22
6-Oct-22	30-Jan-23
6-Oct-22	28-Nov-22
5-Oct-22	28-Nov-22
3-Oct-22	17-Nov-22
3-Oct-22	15-Nov-22
16-Sep-22	27-Oct-22			45
18-Aug-22	8-Nov-22
9-Aug-22	22-Sep-22			60
19-Jul-22	19-Sep-22
18-Jul-22	9-Sep-22
18-Jul-22	20-Sep-22
18-Jul-22	12-Sep-22			25
11-Jul-22	8-Sep-22			150
8-Jun-22	17-Aug-22			25
7-Jun-22	24-Aug-22			60
31-May-22	16-Aug-22			30
31-May-22	26-Jul-22			240
25-May-22	28-Jul-22			25
23-May-22	25-Jul-22
20-May-22	25-Jul-22			25
5-May-22	15-Aug-22			25
20-Apr-22	10-Aug-22			50
15-Apr-22	21-Jun-22			45
12-Apr-22	20-Jun-22			10
4-Apr-22	23-Jun-22			180
22-Mar-22	1-Jun-22			55
11-Mar-22	10-Jun-22
7-Mar-22	14-Jun-22			150
16-Feb-22	16-May-22			25
10-Feb-22	5-May-22			25
10-Feb-22	9-May-22			25
2-Feb-22	2-May-22			45
20-Jan-22	19-Apr-22			45
6-Jan-22	28-Mar-22			25
21-Dec-22	3-Mar-23	3-Mar-23		25
16-Sep-22	27-Oct-22	27-Oct-22		45
14-Sep-22	1-Nov-22	1-Nov-22		90
25-Aug-22	13-Oct-22	18-Oct-22		100
12-Aug-22	23-Sep-22	23-Sep-22		20
9-Aug-22	26-Sep-22	26-Sep-22		60
22-Jul-22	26-Sep-22	29-Sep-22		240
14-Jul-22	23-Sep-22	23-Sep-22		240
12-Jul-22	7-Sep-22	7-Sep-22		90
24-Jun-22	31-Aug-22	31-Aug-22		90

Date is normal date table-

Date	MonthNum	Month	MonthLong	Year	MonthYear	WeekSequenceNum	Week of Year
1/1/2022 0:00	1	Jan	January	2022	Jan-22	1	1
1/2/2022 0:00	1	Jan	January	2022	Jan-22	2	2
1/3/2022 0:00	1	Jan	January	2022	Jan-22	2	2
1/4/2022 0:00	1	Jan	January	2022	Jan-22	2	2
1/5/2022 0:00	1	Jan	January	2022	Jan-22	2	2
1/6/2022 0:00	1	Jan	January	2022	Jan-22	2	2
1/7/2022 0:00	1	Jan	January	2022	Jan-22	2	2
1/8/2022 0:00	1	Jan	January	2022	Jan-22	2	2
1/9/2022 0:00	1	Jan	January	2022	Jan-22	3	3
1/10/2022 0:00	1	Jan	January	2022	Jan-22	3	3
1/11/2022 0:00	1	Jan	January	2022	Jan-22	3	3
1/12/2022 0:00	1	Jan	January	2022	Jan-22	3	3
1/13/2022 0:00	1	Jan	January	2022	Jan-22	3	3
1/14/2022 0:00	1	Jan	January	2022	Jan-22	3	3
1/15/2022 0:00	1	Jan	January	2022	Jan-22	3	3

@Anonymous this is exactly what i was looking for! thanks, how did you create New request, Sent to, Posted calculated columns. i was using this Dax below to create the calculated columns but it doesn't seem to be working. I can see the values as a measure but not as a column . See attached sample data. thanks

One-Dash · ‎03-21-2023

Thanks, It did worked for me to to do what i was looking for

Anonymous · ‎03-15-2023

Hi ,

Please have a try.

New request column =
CALCULATE (
    COUNT ( 'Table'[DATE RECEIVED] ),
    FILTER ( 'Table', 'Table'[DATE RECEIVED] = EARLIER ( 'date'[Date] ) )
)

Please cancel the relationship between the tables.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

One-Dash · ‎03-11-2023

@amitchandak I'm not really good at PBI, can you explain more pleae. How will i create my InitStock? also which columns will be my Order/Sold? the table in above in my question is from excel which i used to populate manually and don't want to use anymore, but hoping PBI can derive something similar. Currently, I only have two tables.

Date table and

Invoice - Ticket#, DateReceived, DateStarted, DateSentQA, DatePosted. example below

Ticket#	DATE RECEIVED	Date Started	Date Sent to Q	DATE Posted
58597974	1/3/2022	4/28/2022	4/28/2022	5/12/2022
58592467	1/3/2022	2/2/2022	2/2/2022	2/3/2022
58593839	1/3/2022	4/20/2022	null	4/20/2022
58560741	1/3/2022	3/25/2022	3/25/2022	5/3/2022
58513117	1/4/2022	4/14/2022	4/14/2022	5/13/2022
58492356	1/4/2022	3/24/2022	3/24/2022	5/3/2022
58571187	1/4/2022	3/23/2022	3/23/2022	5/23/2022
58497924	1/4/2022	4/20/2022	4/20/2022	5/13/2022
58620827	1/5/2022	3/28/2022	3/29/2022	5/3/2022
58519734	1/5/2022	4/20/2022	null	4/20/2022
58532118	1/5/2022	3/24/2022	3/24/2022	5/3/2022
58619507	1/5/2022	4/11/2022	4/11/2022	5/19/2022
57765335	1/5/2022	3/25/2022	null	3/25/2022
58619172	1/5/2022	3/25/2022	3/25/2022	5/3/2022
58550388	1/5/2022	4/20/2022	4/20/2022	5/12/2022

amitchandak · ‎03-10-2023

@One-Dash , You have create cumulative inventory

example

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

or

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

in your case, all might be cumulative

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube