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 dril...
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?

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.