Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Sameperiodlastyear ()

Hello, I want to add a column to calculate inventory value of end of last year. Please advise how to write the Measure. Thank you

amitchandak
4 years ago
Anonymous ,

new column last year same value =

var _date = eomonth([date],-12)

var _company = [company]

return

sumx(filter(Table, [Company] = _company && eomonth([date],0) = _date ) , [Inventory])

YTD a new column =
sumx( filter(Table,[Company] = _company && [Date] <= earlier([Date]) && year([Date]) = year(earlier([Date]) )), [Inventory])

For measure

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Icon for Community Champion rank

Community Champion

4 years ago

Hi Anonymous

Create a column like below:-

Column =
VAR prev =
    YEAR ( [date] ) - 1
RETURN
    CALCULATE (
        SUM ( 'Table (3)'[inv] ),
        FILTER (
            'Table (3)',
            [date] = DATE ( prev, 12, 31 )
                && 'Table (3)'[company] = EARLIER ( 'Table (3)'[company] )
        )
    )

Thanks,

Samarth