Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

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.

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous Ashish_Mathur 

     

    Sorry for the delay in my reply. 

     

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

     

    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

     

     

     

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      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.

       

       

      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.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      Has your question been answered?