Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

previous Year Last day value

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

 

 

 

1 ACCEPTED SOLUTION

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

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 .

 

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

 

stock3.gif

Link to Power BI file here

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

Anonymous
Not applicable

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 .

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors