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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors