previous Year Last day value

[Anonymous]

Hi All,

I got requirement to calculate latest inventory  below condition .

My matrix report structure below : 

Year         2018  2019 2020 

Inventory  50.     60.     70 

Above actually drill down report . Year & month level .

1 . I want show last day of year value 

Ex . In 2018 below it should show only 31 dec 2018 inventory value .

2. again if they drill down to month level I want to see each month last day value 

Ex , report like below 

 Year       2018 

Month     31jan  28 Feb  31 March. .. etc 

Inventory  10       10          5   etc ..

They want to latest value

[Anonymous]

Your requirements are not totally clear... but you might try this:

[Your Transformed Measure] =
SWITCH( TRUE(),
	ISINSCOPE( Date[Month] ),
		CALCULATE(
			[Your Base Measure],
			LASTDATE( Date[Date] )
		),		
	ISINSCOPE( Date[Year] ),	
		CALCULATE(
			[Your Base Measure],
			SAMEPERIODLASTYEAR(
				LASTDATE( Date[Date] )
			)
		)
)

Best

D

[Anonymous]

Hi @Anonymous @stevedep  ,

 In short my requirements are :

I am looking for to find out the latest inventory value of the year .

For ex ,

1.2018 , measure should show the value on 31dec 2018 

2. 2019 , measure should show the 31dec 2019 value 

3. 2020 should show the value captured till last complete month 31 may 2020 only , not the cumlative jan 1 2020 to june 8th 2020 .

[stevedep]

Hi,

Its working on month level, but I am not able (yet) to get it working on year level:

LastestStock = 
var __DateInContext = MAX(DateDimv2[Date].[Date])
var __PreviousMonth = CALCULATE(MAX(DateDimv2[Date]);ALL(DateDimv2[Date]);PREVIOUSMONTH(DateDimv2[Date]))
var __LastKnownDate = 
CALCULATE (
    LASTNONBLANK (
        DateDimv2[Date];
       CALCULATE(SUM('Fact-DailyStock'[Stock]))
    );
    DateDimv2[Date] < __DateInContext
)
var __LastKnownDatePreviousMonth =
CALCULATE (
    LASTNONBLANK (
        DateDimv2[Date];
       CALCULATE(SUM('Fact-DailyStock'[Stock]))
    );
    DateDimv2[Date] < __PreviousMonth
)
return 
if(EOMONTH(TODAY();0)<EOMONTH(__DateInContext;0);BLANK();
    if(
    EOMONTH(TODAY();0) > EOMONTH(__DateInContext;0);
    CALCULATE(CALCULATE(SUM('Fact-DailyStock'[Stock])); FILTER(ALL(DateDimv2[Date]);DateDimv2[Date]=__LastKnownDate));
    CALCULATE(CALCULATE(SUM('Fact-DailyStock'[Stock])); FILTER(ALL(DateDimv2[Date]);DateDimv2[Date]=__LastKnownDatePreviousMonth)))
)  

Link to Power BI file here. 

Hope it helps though (who cares about year level anyways ;-).
Kind regards, Steve. 

[stevedep]

For the latest stock you can look at:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Stuck-in-DAX-Create-calculated-table-from-dat...

[Anonymous]

Hi @stevedep ,

I am looking for to find out the  year last day inventory available value .

Ex, 2020 should show the value captured on 31 may 2020 only , not the cumlative jan 1 2020 to may 31 2020 .

[stevedep · Accepted solution]

Last day inventory is the latest stock part, dateadd minus 1 year to see the value of the year before?