Forum Discussion
Rolling stock
Hello, i need to calculate the rolling stock for this:
the stock before Oct/2019 is =6314
the stock of october need to be =6314+ ENTRATE(october) - CHIUSE(october)
november= october stock +ENTRATE(november) - CHIUSE(november)
how can i do it? thank you very much
Hello @Mcarotenuto89 ,
You can create a measure as follows:
Result = VAR _ent = SUMX ( FILTER ( ALLSELECTED ( 'Test' ), 'Test'[Date] <= MAX ( 'Date'[Mese-Anno] ) && 'Test'[Date] >= DATE ( 2019, 10, 1 ) ), [ENTRATE] ) VAR _chiu = SUMX ( FILTER ( ALLSELECTED ( 'Test' ), 'Test'[Date] <= MAX ( 'Date'[Mese-Anno] ) && 'Test'[Date] >= DATE ( 2019, 10, 1 ) ), [CHIUSE] ) RETURN IF ( MAX ( 'Date'[Mese-Anno] ) < DATE ( 2019, 10, 1 ), 6314, 6314 + ( _ent - _chiu ) )Best regards
Rena
7 Replies
- amitchandak
Super User
Anonymous , for all month after oct it need build like
Cumm Stock = 6314 + CALCULATE(SUM(Sum[ENTRATE]),filter(date,date[date] <=maxx(date,date[date]))) -CALCULATE(SUM(Sum[CHIUSE]),filter(date,date[date] <=maxx(date,date[date])))
With a date calendar. In case you have month generate date from that.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.- amitchandak
Anonymous , you can add like
= [Old formula] + if(Max(Table[Month])="Oct-2019", 3614, 0)
