Combine two columns into one and create running total

[Newcolator]

Hi!

I'm trying to combine data from two columns, in two different tables and then create a running total. My three tables are all joined by Month.

I want to create a running total of value 1 and value 2 that looks like the target. If value 2 is blank I want to take value 1, if value 2 is not blank I want to take value 2. Then I want to create a running total of those values.

I create a DAX formula to combine value 1 and 2. Taking value 1 if value 2 is blank, else taking value 2:

Value 1 or Value 2 = if(ISBLANK(sum('Value 2'[Value 2])), sum('Value 1'[Value 1]), sum ('Value 2'[Value 2]))

Then I create another DAX to create a running total:

YTDTotal = CALCULATE([Value 1 or Value 2], FILTER(ALL('Value 1'), 'Value 1'[Month] <= MAX('Value 1'[Month]) && YEAR('Value 1'[Month]) = YEAR(MAX('Value 1'[Month]))))

But you can see on row 3, when it switches to using value 2, the running total resets, starting again from 20.

Does anyone know how I can do this?

Many thanks.

[JamesFR06]

Can you have a value in 1 or 2 for the same period ?

[Newcolator]

Yes, so you can see in the example that 2024-03 has a value 1 and a value 2. Because value 2 is not blank, I want to select it and ignore value 1.

[JamesFR06 · Accepted solution]

Ok

I have created 2 tables :

T1

T2

And after these 2 measures

Mesure 6 =

var _step1=

SUMMARIZE(t1,T1[Month],T1[Value],"@Valid",

var Per=T1[Month]

return

if(COUNTROWS(filter(t2,and(T2[Month]=Per,T2[Value]<>0)))>0,"No","Yes"))

var _step2=filter(_step1,[@Valid]="Yes")

var _step3=SUMMARIZE(_step2,T1[Month],T1[Value])

return

sumx(union(_step3,T2),T1[Value])

Mesure 7 = calculate([Mesure 6],DATESYTD(T1[Month]))

for this result

[Newcolator]

That works! Thank you so much! I just wish I understood what you've done, but I can copy it for now and study it later!

[JamesFR06]

No problems.

_step1 ==> Just summarize the table T1 and add a column "@valid" on wich I asked to see if there is some value for this period on T2. If countrows of T2 with this period is >0 then "No" else "Yes"

_Step2 ==> I sorted _step1 only with "Yes" values

_Step3==> Summarize of _step2 to delete the column @valid

and

return is the sum of the value of the UNION of _step3 and table T2

[JamesFR06]

Hi

Why don't you use DATESYTD

calculate([Value 1 or Value 2],Datesytd(Dimdate[Date])

[Newcolator]

Thanks. I've done that, but it still gives the same result.