topic Re: Need a urgent help on DAX that calculates backwards based on total in DAX Commands and Tips

Need a urgent help on DAX that calculates backwards based on total

Srinivas904 — Wed, 28 Feb 2024 14:26:29 GMT

I need a urgent help I am having a requirement based on whatever we selected year, month & date i am having a measure which show untill yestarday date

example : Below i have selected 26/01/2024 so it shows untill 25/01/2024 data so what i need is based on this total i need backward calcualtion the sum of the below table is 120 so i need 120+ -33 on 25/01 and again the taotal 120+(-33) = 87 + -31 on 24/01 .... and so on in this it needs to calculate backwards. can anyone help me please

note: these dates are connected to calendaer date where the relation ties.

25/01/2024	-33
24/01/2024	-31
23/01/2024	-20
22/01/2024	-7
18/01/2024	-10
17/01/2024	-96
16/01/2024	-15
15/01/2024	400
15/01/2024	-44
11/01/2024	-28
10/01/2024	-62
10/01/2024	-6
09/01/2024	400
09/01/2024	-53
03/01/2024	-27
02/01/2024	-16
20/12/2023	-2
19/12/2023	-16
18/12/2023	-232
14/12/2023	-94
13/12/2023	-18
12/12/2023	-35
11/12/2023	-56
08/12/2023	-4

Thanks

Srinivas

Re: Need a urgent help on DAX that calculates backwards based on total

v-weiyan1-msft — Thu, 29 Feb 2024 07:27:20 GMT

Hi Srinivas904 ,

I am not sure if I understood your question correctly.
Based on the sample and description you provided, Please try the following steps:
My Sample:

1.You can create a Calculated table.

DateTable = VALUES('Table'[Date])

There is no relationship between the two tables.

2. Use the following code to create measure.

Backward_Calcualtion = VAR Total = 120 VAR Sel_Date = SELECTEDVALUE ( 'DateTable'[Date] ) VAR Calcualtion = Total + SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[Date] >= Sel_Date ), 'Table'[Value] ) RETURN IF ( ISBLANK ( Sel_Date ), Total, Calcualtion )

The field of the slicer is from DateTable.
When you select "1/25/2024" in the slicer, Result is as below.

When you select "1/24/2024" in the slicer, Result is as below.

Is this the result you expect?
Please correct me if I misunderstood your needs.

Best Regards,
Yulia Yan

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

Re: Need a urgent help on DAX that calculates backwards based on total

Srinivas904 — Thu, 29 Feb 2024 09:02:20 GMT

HI v-weiyan1-msft , Thanks for replying to my message quickly. it is almost similar what you provided but I am attaching an excel to give you a better understanding

in the excel there are 3 columns in that column1 dates in the main table which are connected to calender table and column2 is a measure and column 3 is the based on column 2 sum.

https://docs.google.com/spreadsheets/d/1Cag9VHaLOxddbNrnzpFjYppz5T_sBDoE/edit?usp=sharing&ouid=107184863623556308569&rtpof=true&sd=true

I need to use this new measure in a line graph based on dates to show the quantity present in previous dates. The business wants to visualize this information. Can you please help on this.

Thanks

Srinivas