Exclude next year

[Oomsen]

I'm using the function below and would like to exclude values for the coming year.

= IF(MIN(Dimdate[Maand van jaar])<=MONTH(TODAY());[Cashflow **bleep**]-[Cashflow];""

The function is calculating my cashflow openingbalance. I would only like to see a openingsbalance for months in the past. That is why december is empty. But when i filter on 2019 all values except december are presented. 

 

[Oomsen]

This is the 2018 data. I would like to create the samen overview for 2019. All measures are working except the opening balance (beginsaldo).

[v-jiascu-msft]

Hi @Oomsen,

Should it be 0 for the year 2019? Please try the formula below.

a =
IF (
    MIN ( Dimdate[Maand van jaar] ) <= MONTH ( TODAY () )
        && MIN ( Dimdate[Year] ) <= YEAR ( TODAY () );
    [Cashflow **bleep**] - [Cashflow];
    ""
)

Best Regards,
Dale

[Oomsen]

It should be "0" in 2019 indeed. I have tried this function but power bi say: "the visual element can not be opened".

[v-jiascu-msft]

Hi @Oomsen,

That seems not related to the formula. Can you get something in another visual like Table? 

Can you share a sample file only with limit data that can reproduce the issue?

Best Regards,
Dale

[Oomsen]

I wasn't able to add the document so below the date:

date	opening balance	cashflow	receivable	sales orders	payable	costs	closing balance
1-1-2018	362.441,00	-94.613,00					267.828,00
1-2-2018	267.828,00	-271.768,00			2,00		-3.942,00
1-3-2018	-3.942,00	-298.819,00					-302.761,00
1-4-2018	-302.761,00	-75.327,00					-378.088,00
1-5-2018	-378.088,00	27.366,00					-350.722,00
1-6-2018	-350.722,00	-14.062,00	-170,00		160,00		-365.114,00
1-7-2018	-365.114,00	31.476,00			101.559,00		-435.197,00
1-8-2018	-435.197,00	630.486,00					195.289,00
1-9-2018	195.289,00	-164.167,00	481,00		199,00		31.404,00
1-10-2018	31.404,00	49.742,00	85.097,00		10.984,00		155.259,00
1-11-2018	155.259,00	-65.174,00	143.904,00	-50.679,00	167.045,00		16.265,00
1-12-2018	16.265,00		7.741,00	36.732,00	-	106.045,00	-45.307,00
1-1-2019	-45.307,00			63.000,00	30.079,00	95.490,00	-107.876,00
1-2-2019	-107.876,00			82.500,00		92.253,00	-117.629,00
1-3-2019	-117.629,00					91.312,00	-208.941,00
1-4-2019	-208.941,00					93.996,00	-302.937,00
1-5-2019	-302.937,00					99.100,00	-402.037,00
1-6-2019	-402.037,00					97.426,00	-499.463,00
1-7-2019	-499.463,00					95.468,00	-594.931,00
1-8-2019	-594.931,00					95.464,00	-690.395,00
1-9-2019	-690.395,00					96.008,00	-786.403,00
1-10-2019	-786.403,00					96.805,00	-883.208,00
1-11-2019	-883.208,00					97.282,00	-980.490,00
1-12-2019	-980.490,00					96.975,00	-1.077.465,00

[v-jiascu-msft]

Hi @Oomsen,

Can you share a complete sample file? You can upload it to the cloud drive like OneDrive, GoogleDrive, then share the download link here.

Because your sample data works well. Please refer to the snapshot below.

Best Regards,
Dale

[Oomsen]

That's what i tried but unfortunately it wasn't the solution. As you can see in my example the function works for 2018 december, but not for 2019. So i'm looking for a fix in the formula.  

[v-jiascu-msft]

Hi @Oomsen,

Yes, we can see it. Can you share the file and then we can check?

Best Regards,
Dale

[Oomsen]

I can't share the data because the information is confidentially. I will try to explain the calculation.

"Beginsaldo" means "Opening balance" and is a running total of alle financial transactions - "cashflow" which is the sum of al financial transactions in the specific month. 

[Anonymous]

sample data please