Forum Discussion
Table Return Values Based on 2 Criteria(dates)
Im trying to create a dynamic table, that returns a value based on the slicer selection. In a scenario, I want to show what were the projected sales number from previous 4 weeks, to see the change in numbers. Slicer selection = Current date in table
The return value is also based on the slicer selection, and the date version. And the data will change based on the slicer, while the rows in the table is essentially D-7, D-14, D-21, D-28 (previous weeks). Seems quite a complicated task to do in PBI.
I tried using SELECTEDVALUE, but I think it didnt work. Im attaching a saple of data and what it supposed to look and work as a dashboard in excel
Uploaded a sample of the file
Create a switch measure as below,
Days =
SWITCH(TRUE(),(SELECTEDVALUE(WeekData[Sales Week]))=(SELECTEDVALUE(WeekData[Input Week])),"Current Week",(SELECTEDVALUE(WeekData[Sales Week])-7)=(SELECTEDVALUE(WeekData[Input Week])),"D-7",(SELECTEDVALUE(WeekData[Sales Week])-14)=(SELECTEDVALUE(WeekData[Input Week])),"D-14",(SELECTEDVALUE(WeekData[Sales Week])-21)=(SELECTEDVALUE(WeekData[Input Week])),"D-21",(SELECTEDVALUE(WeekData[Sales Week])-28)=(SELECTEDVALUE(WeekData[Input Week])),"D-28",0)Add your fields to the matrix table included the Days measure, Then use the filter pane and filter days on "is Not 0" so only shows 5 weeks worth of data from the Sales Week day selected. if Days column is not sort correctly use the input weekly to sort the data.
5 Replies
Create a switch measure as below,
Days =
SWITCH(TRUE(),(SELECTEDVALUE(WeekData[Sales Week]))=(SELECTEDVALUE(WeekData[Input Week])),"Current Week",(SELECTEDVALUE(WeekData[Sales Week])-7)=(SELECTEDVALUE(WeekData[Input Week])),"D-7",(SELECTEDVALUE(WeekData[Sales Week])-14)=(SELECTEDVALUE(WeekData[Input Week])),"D-14",(SELECTEDVALUE(WeekData[Sales Week])-21)=(SELECTEDVALUE(WeekData[Input Week])),"D-21",(SELECTEDVALUE(WeekData[Sales Week])-28)=(SELECTEDVALUE(WeekData[Input Week])),"D-28",0)Add your fields to the matrix table included the Days measure, Then use the filter pane and filter days on "is Not 0" so only shows 5 weeks worth of data from the Sales Week day selected. if Days column is not sort correctly use the input weekly to sort the data.Hi Bmejia, could you share your PBI file as well? Im having difficulties in getting the correct format to show the table
Sorry, I am not able to upload files like others in the forum
Maybe this will make more sense.
1st just add a table and add your fields Input Week, Project Sales. (This will show everything)
2nd Add a dropdown filter and add the Sales Week field
3rd Create measure above and add to table
4th go to the filter pane and look for days and filter by “is not” 0 (as print screen above).
5th you should only be able to see the date ranges 7,14,28 etc.. every time you filter by Sales Week selected.
Hi,
I have solved a similar problem in the attached PBI files.
Hope this helps.
