Calculate Opening Balance based off of slicer values/date

[Breardon]

Hi All, 

I am traying to create a column that calculates the opening balance based on the minimum slicer value. Bascially I need either a measure/calculated column that says if the date on my transaction in less then the lowest slicer date then output  Time+disburs+invoiced+write-on else return zero. This is then what will be used to calculate my closing WIP balance. So far I have got;

Opening = if((REMOVEFILTERS(Append1[Date]))<[Min date],(sumx(Append1,Append1[Time])+sumx(Append1,Append1[Disburs])+sumx(Append1,Append1[Invoiced])+sumx(Append1,Append1[Write-on])),0)

Where Min date = calculate(min(Append1[Date]))

This just seems to give me zeros when i run it - but i cant seem to find a way to get this to work if anyone has any ideas??  

Thanks in advance!

[amitchandak]

@Breardon , better to use a date table .

Opening = calculate(sumx(Append1,Append1[Time])+sumx(Append1,Append1[Disburs])+sumx(Append1,Append1[Invoiced])+sumx(Append1,Append1[Write-on])
filter(all(Append1[Date]), Append1[Date]<[Min date]))
or
Opening = calculate(sumx(Append1,Append1[Time])+sumx(Append1,Append1[Disburs])+sumx(Append1,Append1[Invoiced])+sumx(Append1,Append1[Write-on])
filter(all(Append1), Append1[Date]<[Min date]))

or

Opening = calculate(sumx(Append1,Append1[Time])+sumx(Append1,Append1[Disburs])+sumx(Append1,Append1[Invoiced])+sumx(Append1,Append1[Write-on])
filter(all(Append1[Date]), Append1[Date]<[Min date]))
or
Opening = calculate(sumx(Append1,Append1[Time])+sumx(Append1,Append1[Disburs])+sumx(Append1,Append1[Invoiced])+sumx(Append1,Append1[Write-on])
filter(all(Date), Date[Date]<[Min date]))

[Breardon]

Thanks for that! So close - still throwing zeros! I tried with the date table too..

[v-xuding-msft]

Hi @Breardon ,

Can you please share some sample data and your expected results? We are not clear just based on a matrix screenshot.  Please share more details with us. Thanks!

[Breardon]

Hi All,

Ive done some more work to the file and have found that if there is no transaction after the opening date and before the closing date then the opening date visual doesnt show nor is it included in the closing balance calculations.

For example, the opening balance for example company 1 should be 66.67 when the opening date is set to 30/04/2020. However, when the closing date is anything before 05/05/2020 it removes it from the visual/ from the calculation in the closing balance measure.

Please see attached link to the file. https://drive.google.com/drive/folders/1Bq-_g5rQg4txyHPM1IiC3sudyznj7f0j?usp=sharing

@v-xuding-msft

@amitchandak

[Breardon]

Hi All, 

Has anyone been able to figure out my problem? 

Thanks

[amitchandak]

@Breardon , Can you share sample data and sample output in table format?

[Breardon]

Is this what you were after?