cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Calculate Opening Balance based off of slicer values/date

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!
7 REPLIES 7
Super User IV
Super User IV

@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]))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Breardon_0-1598961362371.png

 

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!

 

 

 

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

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

Hi All, 

 

Has anyone been able to figure out my problem? 

 

Thanks

Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Breardon_1-1598958962607.png

Is this what you were after? 

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors