topic Re: Get a Prior Period Value based off of A Slicer in DAX Commands and Tips

Get a Prior Period Value based off of A Slicer

jslade — Thu, 03 Mar 2022 00:13:47 GMT

I'm struggling to get my DAX to work. I'm trying to use a measure to return a value from a prior year based off of the slicer the user picks. The slicer is the number of years back they want to look at (so current year - 1 or -2, etc). When I tried the LOOKUPValue function I got "A table of mltiple values was supplied where a single value was expected error", when I added the measure to the table with multiple dates in it. Then I tried

VAR YrsBack = SELECTEDVALUE(Years[Years])
RETURN CALCULATE(VALUES(Query1[value]),
FILTER( ALL (Query1),
Query1[Date] = DATEADD(Query1[Date], (-364 * YrsBack) + -7 * ROUND(YrsBack / 5.5, 0), day))

which doesn't error, but I'm not getting any results in the table. I know I can get it to work by creating a column for each prior year, but was hoping I could do it thru a measue so that I don't have to keep adding a new column every year.

Re: Get a Prior Period Value based off of A Slicer

VahidDM — Thu, 03 Mar 2022 01:05:19 GMT

Hi jslade

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: Get a Prior Period Value based off of A Slicer

jslade — Thu, 03 Mar 2022 16:02:17 GMT

Thank you VahidDM Below is the data I'm playing with and an image of my test PBIX. I'm trying to get a value for the same day last year or 2 or 3 years for the dates in my table. I can't use the sameperiodlastyear function because they want it to be the same day of the week comparision (so Saturday to Saturday and Sunday to Sunday) They also want to be able to compare any year to another year, so I was hoping to use some slicers for them to pick the year they want as the base/ current year and then the number of years back that they want to compare it to. As I mentioned when I tried the LookupValue function I kept getting the multiple values error, but when I try to do a calculate with a filter I don't get any results back (I'm guessing because it is returning for a prior year and my year slicer is filtering on the current year). I'm expecting for the first row in my table (Saturday, January 15, 22) to return Saturday, January 16, 2021 when they pick 1 for the Years Back Slicer and Saturday, January 18, 2020 when they pick 2 for the Years Back Slicer. I am able to get it to work if I do it as sperate columns in my table, but am hoping to do it as a messure so I don't have to add a column at the beging of each year.

Re: Get a Prior Period Value based off of A Slicer

v-chenwuz-msft — Mon, 07 Mar 2022 08:37:08 GMT

Hi jslade ,

I apologise as I am not entirely understanding why you want to use lookeupvalue function, but to fix the error, you can replace the calculate with calculatetable(). The result is a table and also the values(). Then use the calcualtetable() as the filters of calculate(). Something like the following.

a = VAR YrsBack = SELECTEDVALUE ( Years[Years] ) RETURN CALCULATE ( SUM ( Query1[value] ), CALCULATETABLE ( VALUES ( Query1[date] ), FILTER ( ALL ( Query1 ), Query1[Date] = DATEADD ( Query1[Date], ( -364 * YrsBack ) + -7 * ROUND ( YrsBack / 5.5, 0 ), DAY ) ) ) )

Best Regards

Community Support Team _ chenwu zhu

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

Re: Get a Prior Period Value based off of A Slicer

jslade — Mon, 07 Mar 2022 15:57:48 GMT

Thank you for the repsonse. That solution did fix my error, but unfortantly, I just got blank results back. I ended up just using the LookupValue function as a new column in my table, so I have a column for each value (prior year value, prior 2 year value, etc). That seems to be working; it's not ideal, but at least I'm able to get the results they are looking for.