Translate Daily Balance into Daily movement

[Setname]

Hi All,

I have a table with Daily cash balance but I need to calculate daily movement by using ending balance minus beginning balance using DAX language.

For example, as below table, I want to know what is the daily movement for 2/1/2020 is 31-20 =11 for country A.

Could someone help with this please? I can't seem to find an option to upload the pbix file but I attach the demo table as below, thanks!

Date	Country	Balance USD
1/1/2020	A	20
2/1/2020	A	31
3/1/2020	A	56
4/1/2020	A	87
5/1/2020	A	106
6/1/2020	A	156
7/1/2020	A	256
8/1/2020	A	366
9/1/2020	A	458
10/1/2020	A	510
11/1/2020	A	656
12/1/2020	A	784
13/1/2020	A	856
14/1/2020	A	925
1/1/2020	B	20
2/1/2020	B	25
3/1/2020	B	35
4/1/2020	B	50
5/1/2020	B	70
6/1/2020	B	95
7/1/2020	B	125
8/1/2020	B	160
9/1/2020	B	200
10/1/2020	B	245
11/1/2020	B	295
12/1/2020	B	350
13/1/2020	B	410
14/1/2020	B	475

[v-stephen-msft]

Hi @Setname ,

You can try my calculated column. The ALLEXCEPT function is to increase the company’s classification, you can also add other classifications yourself.

Daily = 
VAR tt =
    CALCULATE (
        MAX ( 'Table'[Balance USD] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Company], 'Table'[Country] ),
            [Date] < EARLIER ( 'Table'[Date] )
        )
    )
RETURN
    [Balance USD] - tt

Best Regards,

Stephen Tao

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

[Setname]

Hi, bump up the thread, thanks for helping

[amitchandak]

@Setname , Try a new column like

Daily =
var _max =maxx(filter(Table, [country] = earlier([country]) && [Date] <earlier([Date])), [Date])
return
[Balance USD] - maxx(filter(Table, [country] = earlier([country]) && [Date] =_max), [Balance USD])

[Setname]

Thanks @amitchandak . I tried this in my real table in pbix and it doesn't seem to be correct...

In my fact table, there is actually more than 1 dimensions other than "Company", is it possible to create a new measure which can be put in to the graph or table and have different dimensions as slicers to show Daily movement in different scenarios?

Thanks!