Balance begining different dates

[ifritponyboy]

Dear Mr. Power BI user,

Nowadays I am trying to build a model in power BI and one thing is making me think a lot and making me crazy.

It is about dates, let me explain. We are several companies and I am appending all the accounting dairies. The problem is that if I want to know the result of all of them separately (company, result) I want to have the result for each one (and prior years) depending on when they have the account closed.

For example: company A closed 31st jan account, and the company B has 28th feb. How can I make a table with company, result CY, result PY only showing closed accounts. I cannot have only one calendar filter...

I hope you will answer, I would appreciate very much.

Best regards, keep powering

[TeigeGao]

Hi @ifritponyboy ,

Could you please share some sample data and expected result to us for analysis? I think I can't understand your requirement well.

If we need to have many calender filter, we can create multiple calender filter without relationship.

Best Regards,

Teige

[ifritponyboy]

Table A. Company 1

Date

Amount

Table B. Company B

Date

Amount

Table C. Append Company A+B (I am appending 20 companies)

Table Calendar

Date

Parameter

Last available date Company A 31/01/2019

Last available date Company B 28/02/2019

I want to have a table like this:

Company A...Amount 2019: from 01/01/2019 to 31/01/2019 ...Amount 2018: from 01/01/2019 to 31/01/2019

Company B..Amount 2019: from 01/01/2019 to 28/02/2019 ...Amount 2018: from 01/01/2019 to 28/02/2019

I found a solution having a column in Table A and Table B comparing date to the the correpondant parameter and give me a 1 if it is a date less or equal to the parameter or 0 if it is higher. And another column calculating the relative year and month compared to the date of the parameter (if parameter year=2018 month=1 and date in table A is year 2017 and month 12 it gives me in RelativeYear column a -1 and RelativeMonth column a -1).

The thing is that I am looking for a better solution because when I change a parameter it lasts a bit.

Thanks!!!!