problem with calculating last 4 weeks pased on week number (no calendar)

[Florek]

Hello,

i am trying to calculate last 4 weeks, i need it to be danamic, so whatever week i choose (year-week) i have right last 4 weeks for that date. I have created field week_id that counts continuously week number so year change dont disturb my calculation. my formula

sales_L4W = CALCULATE([sales],

        FILTER(ALL('total_data'),

            'total_data'[week_id]<=SELECTEDVALUE('total_data'[week_id])

            && 'total_data'[week_id]>=SELECTEDVALUE('total_data'[week_id])-3)

)

it shows corect values but it works only on week level. if i add any other data like for example region, it shows total values for each region so i changed it

sales_L4W = CALCULATE([sales],

        FILTER(ALL('total_data'),

            'total_data'[week_id]<=SELECTEDVALUE('total_data'[week_id])

            && 'total_data'[week_id]>=SELECTEDVALUE('total_data'[week_id])-3

            && 'total_data'[region]=SELECTEDVALUE('total_data'[region))

)

ok its include region now, but total disapiered and in different view where i have only weeks it shows empty column.

is there different way to write this formula so i don't have to add all possible data to filter? what happened with total?

[vicky_]

IF the weeks is the only part that needs to be altered in the filter, you can change your DAX to something like:

sales_L4W = CALCULATE([sales],
        FILTER(ALL('total_data'[week_id]),
// ...etc

[Florek]

Hi Vicky,

thanks for Your answer!

I was trying this earlier as well, the result is that formula returns value only for choosen week

[Anonymous]

Hi @Florek ,

Please try this DAX:

sales_L4W = 
CALCULATE(
    [Sales],
    ALL(total_data),
    'total_data'[week_id] <= SELECTEDVALUE(total_data[week_id]) && 'total_data'[week_id] >= SELECTEDVALUE(total_data[week_id]) - 3 && 'total_data'[Region] IN VALUES(total_data[Region])
)

And the final output is as below:

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

[Florek]

Hello Dino

it worked to repair total value, 🙂 thanks

but the main question is how to avoid including all additional perspectives in formula,

when i add to table category for example, its incorrect again until i add it to formula

[Anonymous · Accepted solution]

Hi @Florek ,

Because you used ALL in DAX, if you want to avoid including all additional perspectives in formula, either you can only put all the fields that may affect it into DAX, or you can refer to the following method:
Add another table for slicer using this DAX:

Slicer = VALUES(total_data[week_id])

Then you can change the DAX into this without ALL function:

sales_L4W = 
CALCULATE(
    [Sales],
    total_data,
    'total_data'[week_id] <= SELECTEDVALUE(Slicer[week_id]) && 'total_data'[week_id] >= SELECTEDVALUE(Slicer[week_id]) - 3
)

And the final output is as below:

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

[Florek]

It worked! thank You