Forum Discussion

Josh_Ped's avatar
Josh_Ped
New Member
3 years ago

Balance sheet graph stop at current month

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

 

Need Help

7 Replies

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    3 years ago

    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)
	)

     

  • Josh_Ped's avatar
    Josh_Ped
    New Member
    3 years ago

    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's avatar
      sevenhills
      Icon for Super User rankSuper User
      3 years ago

      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's avatar
        Josh_Ped
        New Member
        3 years ago

        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