Forum Discussion
Solved
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...
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.
That worked a treat. Thanks so much!