Forum Discussion

AndrejZitnay's avatar
AndrejZitnay
Post Patron
7 years ago

Interactive table is needed

Hello all,   Would you be so kind and help me with interactive table?   My Dashboard should contains interactive table, Lease Liability at beginning at 1st month & Lease Liability at 31.03.2019 ...
Need Help
AndrejZitnay's avatar
AndrejZitnay
Post Patron
7 years ago

Hello v-chuncz-msft ,

 

 

Thanks for your help.

 

I've used bellow table. Problem is that There are all dates of my lease between start & end date.

I need to have one row per one month. In this case between Aug - 18 & Aug - 20.

With another asset will be range different.

 

Table =

VAR c =

CALENDARAUTO ()

RETURN

GENERATE (

'Full Lease List',

FILTER ( c, [Date] >= 'Full Lease List'[Start Date] && [Date] <= 'Full Lease List'[End Date] )

)

 

Any advice on that?

 Here is link for PBI file. Password is 0000

https://ulozto.net/!iAMM9MEpOElD/cashflow-1-2-pbix

 

Once I will have table in correct order than I will need to find out way to calculate actual depreciation.

I must have in August month 0 and first deduction will be from September.

 
 
Many thanks for your kind help.
 
Much appreciated.
 
Andrej
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
7 years ago

AndrejZitnay 

 

Just add a calculated column.

Column =
DATE ( YEAR ( 'Table'[Date] ), MONTH ( 'Table'[Date] ), 1 )
  • AndrejZitnay's avatar
    AndrejZitnay
    Post Patron
    7 years ago

    Hello v-chuncz-msft ,

     

    Many thanks for your help. I really appreciate that as this currently my very urgent project.

    I will close another query as items are related as I need both tables and running deprecation and totals for March - 19

     

    Here is my file. Password is still 0000

    https://uloz.to/!2qnn9chBI2LN/cashflow-1-3-pbix

     

    Now I just need last 3 things.

     

    1st - Monthly count. 1st month should be zero, 2nd month should be 1, 3rd month should be 2nd etc.

               (In this example Aug 18 - 0, Sep 18 - 1, Oct 18 - 2)

     

    2nd - Cashflow should be Monthly payment but have to go from Month 1 as per above.

                (In this example from Sep 18)

     

    3rd - Interest. Start in Month 1. (PV*Rate)/12. (As per excel at top of this query)

     

    4th - Lease. (Cashflow - Interest (As per excel at top of this query)

     

    5th - Lease Liability. In month 0 it is PV and from month 1 Lease Liability - lease (As per excel at top of this query)

     

    Sorry for so many requests but I struggle with this quick measures. I've tried deductions but it didn't worked for me.