How to Calculate the Sum/count till selected date

[JenniferWallace]

 I am trying to write a dax to calculate sum of the records. But the sum only takes the latest record for each "Name"

Example : 

I have input as 3 columns Name, Type and Valueupdateddate 

For CP123, Value updated date = 20 Jan 2021, type = manual , and it changed in Feb 2022 i.e, type=automatic , so when calculated for Jan 2022 it should still consider the "Type"  as manual as at that point in time the "Type" was manual.

When I select filter as July 2022. My output table should be. So it should also consider the values if the type has changed.

Can you please help!.

Thanks😊

[tamerj1]

Hi @JenniferWallace 

you need a 'Date' table linked with the main 'Table' then you can use a matrix visual, place 'Date'[Month-Year] in the columns and 'Table']Type] in the rows then place the following measure in the values

Count =
VAR CurrentDate =
MAX ( 'Date'[Date] )
VAR CurrentNames =
CALCULATETABLE ( VALUES ( 'Table'[Name] ), ALL ( 'Date' ) )
RETURN
SUMX (
CurrentNames,
VAR MaxDate =
CALCULATE (
MAX ( 'Table'[Value_Updated Date] ),
ALL ( 'Table'[Type] ),
ALL ( 'Date' )
)
VAR ThisDate =
CALCULATE ( MAX ( 'Table'[Value_Updated Date] ), ALL ( 'Date' ) )
RETURN
IF ( ThisDate <= CurrentDate && ThisDate >= MaxDate, 1 )
)

[JenniferWallace]

@MAwwad It is not a cumulative sum ..If you see the output, it is not increasing, if the type changes, the number decreases.

Here is the sample data. 

Name	Type	Value_Updated Date	Latest Record?
CP123	Manual	20-Jan-21	No
CP123	Automatic	21-Feb-22	Yes
CP124	Manual	20-Jan-21	No
CP124	Automatic	05-Apr-22	No
CP124	Manual	07-Apr-22	Yes
CP130	Manual	17-Oct-21	No
CP130	Automatic	01-Dec-21	Yes
CP138	Automatic	09-Apr-22	No
CP138	Manual	09-Jun-22	Yes

[MAwwad]

To create a cumulative sum in DAX (Data Analysis Expression) language, you can use the SUMX function, which iterates over a table and calculates a running sum of the expression provided.

For example, to calculate a cumulative sum of the "Value" column for a table with the input columns "Name", "Type", and "Valueupdateddate", you could use the following DAX formula:

CumulativeSum = SUMX ( FILTER ( TableName, TableName[Valueupdateddate] <= MAX ( TableName[Valueupdateddate] ) ), TableName[Value] )

This formula filters the table to only include rows with a "Valueupdateddate" less than or equal to the maximum "Valueupdateddate" in the table, and then calculates a running sum of the "Value" column.

If you want to further filter the table by a specific date range, such as July 2022, you can modify the formula to include an additional filter on the "Valueupdateddate" column, like this:

CumulativeSum = SUMX ( FILTER ( TableName, TableName[Valueupdateddate] <= MAX ( TableName[Valueupdateddate] ) && TableName[Valueupdateddate] >= DATE ( 2022, 7, 1 ) && TableName[Valueupdateddate] <= DATE ( 2022, 7, 31 ) ), TableName[Value] )

This formula filters the table to only include rows with a "Valueupdateddate" in July 2022, and then calculates a running sum of the "Value" column.