Track same batch of records over time

[Kevin000]

Hello,

I'm trying to create a DAX formula to track records that existed on a selected date, and their values on other dates.

In the example below, assuming I have selected the date "11/1/2023", I would like to track the values from DocumentNumber's {0001, 0002, 0003, 0004} and there values on future dates.

Data sample:

DocumentNumber	AgeDate	Value
0001	11/1/2023	10
0002	11/1/2023	10
0003	11/1/2023	10
0004	11/1/2023	10
0001	11/7/2023	10
0002	11/7/2023	10
0003	11/7/2023	-
0004	11/7/2023	-
0005	11/7/2023	7
0006	11/7/2023	7
0007	11/7/2023	7
0008	11/7/2023	7
0001	11/14/2023	-
0002	11/14/2023	10
0003	11/14/2023	-
0004	11/14/2023	-
0005	11/14/2023	7
0006	11/14/2023	7
0007	11/14/2023	7
0008	11/14/2023	7

Desired Output 

11/1/2023	40
11/7/2023	20
11/14/2023	10

I'm using power pivot excel datamodel. To keep this simply, no table relationships, no date table.

This is my attempt at the DAX, but it obviously doesn't work.

[BatchAmount]
=VAR vList = 
FILTER( VALUES(CombinedAging[DocumentNumber]),Min(CombinedAging[AgeDate]) = [SelectedBatchDate])
RETURN
CALCULATE([Amount],CombinedAging[DocumentNumber]=vList)

[SelectedBatchDate] evaluates to "11/1/2023" (I have a slicer connected to the pivot table, but the slicer references a table of dates without any relationships in the datamodel)

Thank you in advance for your help.

[v-nuoc-msft · Accepted solution]

Hi @Kevin000

@VahidDM has already proposed a solution, nice method! And here's my solution:

First, create a date table that is not related to the primary table

 Table 2 = CALENDAR(MIN('Table'[AgeDate]),MAX('Table'[AgeDate]))

Here is the primary table and DAX,  filter out the DocumentNumber in the 11/1/2023 date to define it as _NUMBER, and then sum the corresponding values of the DocumentNumber in the _NUMBER range

Measure = var _NUMBER=SELECTCOLUMNS(FILTER(ALL('Table'),[AgeDate]=SELECTEDVALUE('Table 2'[Date])),"DocNum",[DocumentNumber])
return CALCULATE(SUM('Table'[Value]),FILTER('Table',[DocumentNumber] in _NUMBER))

Add a slicer visual

Add a table visual, and select 11/1/2023 in slicer, here are the results

Best Regards,

Nono Chen

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

[Kevin000]

Nono,

Thank you for replying with a nice solution. I didn't test the solution, but I can see that it will work as you show. 

EDIT: Your solution is the one which works best for my situation and produces the correct results. I believe your solution works because the dax is using SELECTEDCOLUMNS rather than CALCULATETABLE 

I modified the DAX slightly as shown below

[BatchAmount2]

=VAR _vSelectedDate = MIN(BatchDate[BatchDate])

VAR _vNUMBER=
	SELECTCOLUMNS(
		FILTER(
			ALL(CombinedAging),
			[AgeDate]=_vSelectedDate
		),
			"DocNum",[DocumentNumber]
	)
			
VAR _vResult =
	CALCULATE([Amount], 
		FILTER(CombinedAging,[DocumentNumber] IN _vNUMBER
		)
	)
RETURN
        _vResult

The below picture shows a single document record where

[Amount] is a simple SUM('table'[Values])

[BatchAmount] is VahidDM's solution using CALCULATETABLE

[BatchAmount2] is your solution using SELECTEDCOLUMNS

Notice once the doc ages into the 91-180 days bucket, the row level detail is lost in Vahid's solution, but the grand total is correct. Your solution filters only the documentnumber column rather than the whole table.

Granted, my sample data did not include the fidelity of details needed to anticipate this issue.

Both solutions were very helpful to me and I learned a lot about CALCULATETABLE and SELECTEDCOLUMNS

EDIT2:

I found another oversight in the simplicity of my sample data. Filter context is lost when using Nono's solution. In the picture below, the left pivot is a simple sum of the values. The right pivot is Nono's solution. In the right pivot  'Current' is excluded in the filter, but as this documentnubmer moves into 1-30 days, the values appear. I believe this is because FILTER(ALL(....)) used in the DAX. How can the dax be rewritten to allow filter context such that the yellow highlight would not return?

[VahidDM]

Hi @Kevin000 

Try this DAX:

ValuesOnSelectedDate = 
VAR _SelectedDate =
    SELECTEDVALUE ( 'DateTable'[AgeDate] )
VAR _DocNumber =
    CALCULATETABLE (
        VALUES ( 'Table'[DocumentNumber] ),
        'Table'[AgeDate] = _SelectedDate
    )
VAR _Result =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[DocumentNumber] IN _DocNumber )
RETURN
    _Result

Output:

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Kevin000]

Vahid,

Your solution works great. I modified it slightly only because SELECTEDVALUE isn't available in PowerPivot, but it works exactly as I had hoped. Thank you very much for your help.

=
VAR _vSelectedDate = MIN(BatchDate[BatchDate])

VAR _vDocNumber =
	CALCULATETABLE(
		VALUES(CombinedAging[DocumentNumber]),
		CombinedAging[AgeDate] = _vSelectedDate
	)

VAR _vResult =
	CALCULATE([Amount], CombinedAging[DocumentNumber] IN _vDocNumber)

RETURN
    _vResult

[VahidDM]

@Kevin000 

Glad to see your problem has been solved.

it would be great if you mark my solution and your as solutions to help other people with similar issues.

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube