LOOKUPVALUE with measure

[Anonymous]

I want to use a similar function than LOOKUPVALUE that works with measures.

My objective is to display the LAST year's value

I have a Dataset like this:

There is a relationship between the Two 'Date' collumns.

I use measures to compile data for a specific region (and other parameters in my full dataset): 

Sales A Measure = CALCULATE(SUM('Table'[Sales]), 'Table'[Region] == "A")

I want to have a 'Sales A Last year' value:

I want to use the 'Date match + 1' as a reference to get the last year's value.

How can i do That ?

[v-yetao1-msft]

Hi @Anonymous 

Could you provide a specific sample ? This can help us better deal with the problem .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I've edited the main post, tell me if that is enough. I don't think i can link the exemple pbix ...

[m3tr01d]

@Anonymous Can you give us an example where the result is wrong because of leap year and tell us what result you would like to have.

[amitchandak]

@Anonymous , Try measures like

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

or for YTD

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"))

[Anonymous]

The problem is DATEADD and SAMEPERIODLASTYEAR are incorrect for me as they do not work with leap years AND my measure is a bit more complex than a SUM

[Ashish_Mathur]

Why will the SAMEPERIODLASTYEAR() not work?  In what situation will it fail?

[Anonymous]

to @Ashish_Mathur

So when i tried:

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

I don't get the same day of the week.

this makes next year look like this :

And it's shifted from what i want, i want the same day of the week; tuesday to tuesday.

The server i'm pulling my data from gives me the 'Next year corresponding day', that is correct for me, hence why i want to just match the collumn