CALCULATE TABLE with Filter

[LUCASM]

I am tring to create a dynamic table from my source table based on a date slicer

The date slicer [Period] looks like this "Week 38 2022" - pretty useless in itself

I do also have another numeric column called [YYYYWW] which in this case would be 202238 which I am using in my DAX

So I tried this first to see what happens:

13WeekFunctionTable = 
var ed = 202239
var sd = ed-12

RETURN
CALCULATETABLE (
    SUMMARIZE('TABLE',[YYYYWW],
    [Period],
    [Function Group],
    "Sales Units", SUM('TABLE'[Sales Units]),
    "Sales Values", SUM('TABLE'[Sales Values EUR])
    ),
    FILTER (
        TABLE,
        TABLE[YYYYWW] >= sd
            && TABLE[YYYYWW] <= ed
    )
)

Great it works fine!

How about if i use the SELECTEDVALUE as the end point ?

13WeekFunctionTable = 
var ed = selectedvalue ('TABLE'[YYYYWW] )
var sd = ed-12

RETURN
CALCULATETABLE (
    SUMMARIZE('TABLE',[YYYYWW],
    [Period],
    [Function Group],
    "Sales Units", SUM('TABLE'[Sales Units]),
    "Sales Values", SUM('TABLE'[Sales Values EUR])
    ),
    FILTER (
        TABLE,
        TABLE[YYYYWW] >= sd
            && TABLE[YYYYWW] <= ed
    )
)

Now I get an empty table!!!

Can anyone tell me what I am missing or doing wrong

[vanessafvg]

can you return the selected value and see what you are getting?  you will need to debug your code.   Maybe your selected value is not returning what you expect.

for your return statement return ed and see what you get.

[LUCASM]

Hi @vanessafvg 

as this DAX is creating a table I cannot return ed
However I am using ed in a calculation which does work as expected

ASP_Last_Week = 
var ed = selectedvalue ( TABLE1[YYYYWW] )

var ed_sales = CALCULATE ( 
     sum ( TABLE1[Sales Values] ) ,
     REMOVEFILTERS ( TABLE1[Period] ) , 
     TABLE1[YYYYWW] = ed )

var ed_volume = CALCULATE(
     sum ( TABLE1[Sales Units]) ,
     REMOVEFILTERS ( TABLE1[Period]) , 
     TABLE1[YYYYWW] = ed )

RETURN 
     DIVIDE( ed_sale, ed_volume )

If I return ed here I get the expected enddate - In this case 202238.

Since asking my question, one of my colleagues - whom I highly respect, has suggested that you cannot use SELECTEDVALUES in a CALCULATETABLE statement as CALCULATETABLE removes/ignores any filters that are in place.

I guess in my case, the Slicer is a filter.

Can anyone comment on this.

[v-yinliw-msft]

Hi  , 

If I understand your idea correctly, you want to use dynamic variables to express the end date?

If so, the problem is from this code:

var ed = selectedvalue ('TABLE'[YYYYWW] )

This code can run in the calculation but DAX do not suppose to use a dynamic variables to create a table.

So if you want to preserve the filter, you can add the filter in the Power BI Desktop.

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

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

[vanessafvg]

sorry it was late and i missed that, he is 100% as you will not have row context which you need for selectedvalue. 

[vanessafvg]

what if you created a variable using this?

var currdte = CALCULATE ( 'table'(date] ), ALLSELECTED( 'table'[date] ))