Balance sheet graph stop at current month

[Josh_Ped]

Hi Guys,

I have a graph of total value of stock, and i cannot get it to stop at the current fiscal_month.

If have filters on the page for year and month.

The balance is made of periods CF, 0,1,2,3 etc

The balance has to include CF,0 otherwise it is just the current month net movement.

I have tried adding a flag to say if the period is included in the calc, but even that continues the same value on the graph

My current formula is 

Stock on Hand = CALCULATE(sum(TGLBalsheet[balance]),TGLBalsheet[accountid] IN {82,260,232,118},all(months),Months[Fiscal Month] <=Selectedvalue(Months[Fiscal Month]),Years[Year]=SELECTEDVALUE(Years[Year]))

And current graph looks like this, last year calcs just fine using the same formula (just with -1 on the selected year)

Any help is much apreciated

[Josh_Ped]

Thanks @sevenhills , you have gotten me most of the way.

The reason i didnt attach the model was due to the 27 tables it requires 🙂 (it is not just balance sheet in the model).

Below is sample of the Bal sheet table (which i have no modified to include a date field)

I have updated the DAX formula to be this

Stock on Hand2 = 
CALCULATE(
		sum(TGLBalsheet[balance]),
		TGLBalsheet[accountid] IN {82,260,232,118},
		TGLBalsheet[Date]<= EOMONTH ( DATE(SELECTEDVALUE(Years[Year]), Selectedvalue(Months[Fiscal Month]), 1),-2)
	    )

And we are nearly there, it is just not displaying cumulative balances.

The Bal Sheet table is linked to a Calendar table via the date field...see below

Unfortunately the tables are not all adustable, and it would be preferable to have them all have a date field, but most just have a financial year and period as the "Date".

as a side note;

Would it be worth adding a date field to all tables to then link the calendar?

Cheers

[sevenhills]

Let us give a try like this and see if it works

Stock on Hand2 = 
var _cut_off_date = EOMONTH ( DATE(SELECTEDVALUE(Years[Year]), Selectedvalue(Months[Fiscal Month]), 1),-2)

-- RETURN _cut_off_date -- Can you test this value once and see if this is the cut off date, what you need?

RETURN CALCULATE(
         sum(TGLBalsheet[balance]),
         FILTER (allselected(TGLBalsheet),
		TGLBalsheet[accountid] IN {82,260,232,118} &&
		TGLBalsheet[Date]<= _cut_off_date 
	    )
)

[Josh_Ped]

Hi @sevenhills 

The cut off date is giving the expected date (as in we are in Jan, so i wanted everything up until 31/12/2022...which it is doing)

The DAX now give cumulative, but similar to my issue, there is no cutoff, it just keeps going

[sevenhills]

Glad the measure is working!

Still it is not clear with the chart image, what is the output you need?

you are having all 12 months as Jan to Dec and not sure what output you are expecting and which is the cut off date's month.

Since the cut off date and values are correct, Can you try with only the "Stock on Hand2" measure and remove the other two and see?

Other testing you can do is:

Create table visual, with Month-YYYY, TGLBalsheet[balance], cut off date measure and the "Stock on Hand2" measure and see the output you want is working correctly. then change it to image. Later add other measures and see where you are hitting the issue.

Thanks

[Josh_Ped]

Hi @sevenhills ,

Sorry for the late reply have been working on another piece of work.

below are a couple of screenshots of what I am expecting, and a snapshot of the data i am working with;

Data

Expected Graph (made up data). The orange line is already working correctly, it is the blue line (this year's data) that i need to stop at the end of the last completed month.

Currently the blue line continues at the maximum value for all months from Feb onwards

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[sevenhills]

Since you did NOT attach the model, it is tough to get the eaxact DAX.

I kind of wrote the psuedo code, to cut off at the end of the today's month. Hope this helps!

Stock on Hand = 

var _EO_MONTH_today = EOMONTH ( TODAY(), 0)

var _sel_date = EOMONTH ( DATE(SELECTEDVALUE(Years[Year]), Selectedvalue(Months[Fiscal Month]), 1))

var _cut_off_date = IF (_EO_MONTH_today >= _sel_date, _EO_MONTH_today, _sel_date)

RETURN 
	CALCULATE(
		sum(TGLBalsheet[balance]),
		TGLBalsheet[accountid] IN {82,260,232,118},
-- Optional: I would recommend to use cut off date here. It is Okay to use months and year fitler too. 
		all(months),
		Months[Fiscal Month] <= MONTH(_cut_off_date),
		Years[Year] = YEAR(_cut_off_date)
	)