Weekly opening balance

lc_finance although it is now working properly, I noticed that there is a difference with the actual balance. This is because last year's balance is not included. How could I add this?

Hi Oomsen 

I reopened the file and I see that you have a year slicer. Thus if you choose 2019 on the year slicer, all the amounts from 2018 and 2020 will be filtered out.

There is a solution to it, which is using ALL(). This function, along with CALCULATE, allows you to remove filters for a certain formula.

The syntax would be:

CALCULATE( your calculation goes here, ALL(Dimdate[Jaar]))

the calculation you enter will disregard the slicer on the year, it will thus be able to access all years.

Does this help you?

Regards

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

lc_finance i changed the measure to: 

Hi Oomsen ,

is that the result you were looking for?

LC

lc_finance no it's not. 

The closing balance at 201 was -46k. 

It looks like it's making a sum of all years after the addition of ALL.

Hi Oomsen ,

I see that in the Power BI I have, the Transactions table only has data for the year 2019. Can you share a sample of your Power BI with data in multiple years?

That will help me in understanding the issue.

Regards,

LC

lc_finance unfortunately i'm not able to share the complete .pbix .

The problem is that "Dimdate'[Week Nummer]<VALUES('Dimdate'[Week Nummer])" doesn't work for the first week every year. 

It gives a blank on week 1 every year. 

Hi Oomsen ,

I understand that you cannot share it for confidentiality reasons.

But maybe it's possible to share a sample of it? A sample of it with the problem

Seeing the file would make it easier to help you find the issue.

Regards

LC

lc_finance  below my test file. 

The "opening" measure isn't running if you ask me.

In my report i would like to see visuals between today and the next 6 months. 

A relative date filter would be the best solution for this i guess?

.PBIX