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...
Need Help
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

       

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

        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