Filter a text to get the open balance.

[Nun]

Hello,

I have these tables

Table2

Index  Overdue situation                 Ago-20Africa  Ago-20Europe

1         Positive overdue >8 days          

2         Negative overdue >8 days

3         Positive overdue >90 days

4         Negative overdue >90 days

Table1

Customer number Area     >8 days  >90 days  Date            MM_YYYY

1                            Africa      10          -20          31.08.2020  Ago-20

2                            Europe    -3          15            31.08.2020  Ago-20

3                            Europe     15        10             31.08.2020  Ago-20

4                            Asia          -4         30             31.08.2020  Ago-20

5                            Africa       -30       15               31.08.2020  Ago-20

6                             Africa       -5       -10               31.08.2020  Ago-20

In Table 2 column "Ago-20Africa" the output should filtering the Area, for example Area = "Africa"

.

Index  Overdue situation                 Ago-20Africa  Ago-20Europe

1         Positive overdue >8 days     10       

2         Negative overdue >8 days   -35

3         Positive overdue >90 days    15

4         Negative overdue >90 days  -30

I am using the measure: 

Aug-20Africa = var test = CALCULATE(FIRSTNONBLANK ( 'Table1'[Customer number]; 1 );

FILTER(ALL('Table1'); 'Table1'[Area] = "Africa")) return IF([Index] = 1;CALCULATE(SUM('table1'[>8 days]);'table1'[>8 days] > 0)/1000;IF([Index] = 2;CALCULATE(SUM('table1'[>8 days]);'table1'[>8 days] < 0)/1000;IF([Index] = 3; CALCULATE(SUM('Table1'[>90 days]);'Table1'[>90 days] > 0)/1000;if([Index] = 4;CALCULATE(SUM'Table1'[>90 days]);'Table1'[>90 days] < 0)/1000))))))

the result is that I get the same value no matter which "Area" I use as filter.

Thank you in advance!

[Greg_Deckler]

@Nun So what should the output be for the given data? Can you post an image of your data model? You might be missing a relationship or have a relationship direction that needs changed.

[Nun]

@Greg_Deckler 

the otuput, filtering the Area in Table1 (Africa in Table2 "Ago-20Africa), Europe in Table2"Ago-20Europe")

Index  Overdue situation                  Ago-20Africa        Ago-20Europe

1         Positive overdue >8 days      10                               15

2         Negative overdue >8 days    -35                             -3

3         Positive overdue >90 days    15                             25

4         Negative overdue >90 days   -30                           null

table1

table2

Using the previous formula, I am getting the same results in table2, if I filter Area "EMEIA" or "Americas" or "APAC" in column Ago-20 table2.

Thanks.

[v-rzhou-msft · Accepted solution]

Hi @Nun 

I build Table1 and Table2 like yours to have a test.

Table1, MM_YYYYcolumn is a calculated column.

MM_YYYY = FORMAT(Table1[Date],"MMM")&"_"&FORMAT(Table1[Date],"YY")

Table2:

I build two measures to achieve your goal.

Aug_20Africa = 
var _P8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>8 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>8 days]<0&&Table1[MM_YYYY]="Aug_20"))
var _P90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>90 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>90 days]<0&&Table1[MM_YYYY]="Aug_20"))
return
SWITCH(MAX(Table2[Index]),1,_P8,2,_N8,3,_P90,_N90)

Aug_20Europe = 
var _P8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>8 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>8 days]<0&&Table1[MM_YYYY]="Aug_20"))
var _P90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>90 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>90 days]<0&&Table1[MM_YYYY]="Aug_20"))
return
SWITCH(MAX(Table2[Index]),1,_P8,2,_N8,3,_P90,_N90)

Result is as below.

You can download the pbix file from this link: Filter a text to get the open balance.

Best Regards,

Rico Zhou

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

[Nun]

@v-rzhou-msft 

thanks a lot for your solution, istead to use SWICTH I used IF, because for some reason  I got the same (negative) total for all the var.

So the I used: 

return

IF([Index] = 2;_P8;IF([Index]=3;_N8;IF...

Thanks!