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

     

    • Josh_Ped's avatar
      Josh_Ped
      New Member
      3 years ago

      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