Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Last 5 weeks calculation based on slicer

Hello all,

I need help with a dax calculated column which I can use as a filter field. I have the following data:

Date

Week Number

Custom Week

Values

26-11-2021

48

47

1

25-11-2021

48

47

5

24-11-2021

48

46

6

23-11-2021

48

46

7

22-11-2021

48

46

8

21-11-2021

48

46

9

20-11-2021

47

46

33

19-11-2021

47

46

20

18-11-2021

47

46

5

17-11-2021

47

45

7

16-11-2021

47

45

9

15-11-2021

47

45

43

14-11-2021

47

45

1

13-11-2021

46

45

2

12-11-2021

46

45

3

11-11-2021

46

45

6

10-11-2021

46

44

5

09-11-2021

46

44

8

08-11-2021

46

44

10

07-11-2021

46

44

15

06-11-2021

45

44

16

05-11-2021

45

44

33

04-11-2021

45

44

23

03-11-2021

45

43

5

02-11-2021

45

43

5

01-11-2021

45

43

5


“Week number” is the usual week number, the Custom week is the week number I’m using- the start date is from Thursday, end day is Wednesday.  Dates+weeks come from one table and “Values” comes from a different one.

When I select week 48 from the slicer, I need the bar graphs to show 4 last week’s  + the selected one (example below)

 

 

 

 

 

 

 

 

 

 

If I select from the slicer Week 47 – I need to see bars for Week 47,46,45,44,43.

Thank you.

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    --"“Values” comes from a different one" .

    Does this other table have week column that can correspond to the date_week table?

    If so, you could create a measure as below.

    measure = IF(selectedvalue(Table[custom])>=selectedvalue(date_week[custom])-4&&selectedvalue(Table[custom])<=selectedvalue(date_week[custom]),1,0)

    Use the custom week in value table as x-axis and the custom week in date_week table as slicer.

    Then add the above measure to visual filter set measure = 1.

     

    Best Regards,

    Jay

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    --"“Values” comes from a different one" .

    Does this other table have week column that can correspond to the date_week table?

    If so, you could create a measure as below.

    measure = IF(selectedvalue(Table[custom])>=selectedvalue(date_week[custom])-4&&selectedvalue(Table[custom])<=selectedvalue(date_week[custom]),1,0)

    Use the custom week in value table as x-axis and the custom week in date_week table as slicer.

    Then add the above measure to visual filter set measure = 1.

     

    Best Regards,

    Jay