Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pbi07
Helper V
Helper V

Visualizing historical balance

I am building visuals for providing a view of invoice balances based on a date selection. 

 

Based on selected date, the view will show the invoices and the amount owed by customer and a chart with total balances by month. 

Transactions are in the form of this below table. I have a calendar table from 2015.  

Current balances is  available from the balance column. I have the bar chart showing the current balalnces due for each month. But i would need to build something to look at the balance due as of any particular date.  What will be the approach without a performance issue. 

 

 

CustomerInvoiceOrder DateAmountBalancePaid DatePaid Amount
5000INV101/10/20191000001/29/20191000
5010INV501/20/20191500002/12/20191500
5000INV602/05/20192000002/28/20192000
5015INV704/02/201942004200  
5016INV704/10/201937003700  


I have a calendar table with dates from 2015

1 ACCEPTED SOLUTION

Hi @Pbi07 ,

 

Did my formula work for you? Let me know if i miss something.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

10 REPLIES 10
v-jayw-msft
Community Support
Community Support

Hi @Pbi07 ,

 

Please check the following steps.

1# Create a yearmonth column for your CALENDAR table.

 

yearmonth = FORMAT('CALENDAR'[Date],"YYYYMM")

 

2# Create a measure as below.

 

Measure = CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Invoice]),FORMAT('Table'[Order Date],"YYYYMM")=SELECTEDVALUE('CALENDAR'[yearmonth])||FORMAT('Table'[Paid Date],"YYYYMM")=SELECTEDVALUE('CALENDAR'[yearmonth])))

 

 Result would be shown as below.

3.PNG

If i misunderstand your meaning, please show the expected result to me.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

@v-jayw-msft @Ashish_Mathur 

 

Sorry for the delay in my reply. 

 

The ask is to show the Due amount details based on a date .  

 

A1.JPG

From the above data, if the date selected is 11/20/2017, only the first transacation should be displayed as the $1600 is due as of 11/20/2017 and the rest of the orders belongs to 2019 or they are all greater than 11/202/2017

 

 

 

 

 

Hi,

Has your question been answered?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Pbi07 ,

 

Please chekc the measure below.

Measure = 
var amount = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Order Date]<=SELECTEDVALUE('CALENDAR'[Date])))
var paid = CALCULATE(SUM('Table'[Paid Amount]),FILTER('Table','Table'[Paid Date]<=SELECTEDVALUE('CALENDAR'[Date])))
return
amount-paid

Result would be shown as below.

9.PNG

10.PNG

11.PNG 

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Pbi07 ,

 

Did my formula work for you? Let me know if i miss something.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Ashish_Mathur
Super User
Super User

Hi,

That is a confusing question.  What exact result do you want?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
edhans
Super User
Super User

I would start with another date column in your data.

  1. Create a date column that is the "Latest Activity" which is if there is a payment, use the Payment date, otherwise use the Order Date.
  2. Relate that new date column to your Date column in your Date Table.
  3. You need a measure that shows the balance, which is Amount - Paid Amount. Don't worry about Paid Amount being null. Number-BLANK=Number in DAX.
  4. You can then create a table/matrix/whatever that uses the Date[Date] from your Date table then summarizes or pulls whatever you want from this table below.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

I do not understand having a calendar of dates from 2015 when you data shows from 2019.

 

Unless I am seriously missing something, generally the way you handle what you have presented is to use a date slicer to zero in on particular dates.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.