topic Re: previous Year Last day value in DAX Commands and Tips

previous Year Last day value

Anonymous — Mon, 08 Jun 2020 14:02:43 GMT

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

Re: previous Year Last day value

stevedep — Sat, 06 Jun 2020 08:11:49 GMT

For the latest stock you can look at:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Stuck-in-DAX-Create-calculated-table-from-date-based-last-values/m-p/1132982/highlight/true#M16845

Re: previous Year Last day value

Anonymous — Mon, 08 Jun 2020 14:04:03 GMT

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 .

Re: previous Year Last day value

stevedep — Sat, 06 Jun 2020 09:23:12 GMT

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

Re: previous Year Last day value

Anonymous — Sun, 07 Jun 2020 19:56:03 GMT

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

Re: previous Year Last day value

Anonymous — Mon, 08 Jun 2020 14:08:56 GMT

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 .

Re: previous Year Last day value

stevedep — Mon, 08 Jun 2020 18:47:48 GMT

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.