Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

 

 

DAX Calculation
power bi
Power BI Desktop
  • stevedep's avatar
    stevedep
    6 years ago

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

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        stevedep
        Icon for Memorable Member rankMemorable Member
        6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        stevedep
        Icon for Memorable Member rankMemorable Member
        6 years ago

        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.