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

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

 

I’ve added her links for data source and PBIX.

 

I have PV formula in PBI file.

 

Passwords are : 0000

https://ulozto.net/!UNAmkFEWfcJF/cashflow-xlsx

https://ulozto.net/!MI770rkrVenX/cashflow-pbix

 

 

Idea is that everything will change based on selection of Asset.

Problem is that sometimes is there Lease for 24 months, sometimes 36 months etc.

 

 

Can you help me out?

 

Many thanks.

 

Kind regards.

 

Andrej

 

Table.JPG

4 REPLIES 4
Community Support Team
Community Support Team

Re: Interactive table is needed

@AndrejZitnay 

 

You may take a look at the post below.

https://community.powerbi.com/t5/Developer/Dax-Help-Create-table-with-variable-of-lines-according-to...

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

Re: Interactive table is needed

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.

 
table 1.2.JPG
 
Many thanks for your kind help.
 
Much appreciated.
 
Andrej
Community Support Team
Community Support Team

Re: Interactive table is needed

@AndrejZitnay 

 

Just add a calculated column.

Column =
DATE ( YEAR ( 'Table'[Date] ), MONTH ( 'Table'[Date] ), 1 )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Interactive table is needed

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. 

 

table 1.3.JPG

 

Table.JPG

 

 

Sample.JPG

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 299 members 2,810 guests
Please welcome our newest community members: