Selectedvalue Function returning Null Values

[abhishekrws01]

Hello ,

Please help if possible, I have been trying to solve it for few days now. Its related SelectedValue function. 

Context : I have two relational tables i.e. datilyTxdata (fact) and calendar table. I am trying to create a calculated table and would like to add a column within this table.

Calculated Table 1

Dailyreport = 

VAR DateLevelSummaryTable =

SUMMARIZE(DailyTxData,DailyTxData[BRAND],Region[Display Country Name],Region[Sort Key],DailyTxData[MARKET], DailyTxData[ISSUEDATE],'Calendar'[Week End Date],DailyTxData[TRAFFICTYPE],DailyTxData[GEO_NAME],"WD at Day Level",MAX(DailyTxData[Weekdays]) , "Gross TX" , SUM(DailyTxData[GROSS_TRANSACTION]) , "Net TX" , SUM(DailyTxData[NET_TRANSACTION]),"Net Sales", SUM(DailyTxData[NET_SALES]) )

Return

DateLevelSummaryTable  - This works fine

Then I have a date slice :  Slicer Select Date = SELECTEDVALUE('Calendar'[Date]) . This works fine when I visualize it

but doesn't work when I use it for column creation.

Here is where I need help please:

Calculated Table 2

Dailyreport2 =

Var statedate = CALCULATE([Slicer Select Date])

VAR A =

    ADDCOLUMNS(Dailyreport,"ReportingWeek" , DATEDIFF(Dailyreport[ISSUEDATE], statedate,DAY ), "Grouping" , If(DATEDIFF(Dailyreport[ISSUEDATE], statedate,DAY )<7 , "L7" , "XX"))

Return

A

 I get blank value in the table for "ReportingWeek". 

Hope this makes sense. 

Thank you for your help in advance.

[HotChilli]

Slicers cannot affect calculated columns or tables.

Slicers are for visuals and measures.

[abhishekrws01]

Hi - Thank you for your response. Can you or anyone in the community please suggest some way how I can achieve this? I am trying to dynamically calculate sales, working days and a few other KPIs for the last 6 weeks. In the example below, the selected date is 30-Jan-2023. If the user selects another day, then these dates will move accordingly.  Can I use Parameter or separate table etc?  

Thank you for your help in advance.

[v-stephen-msft · Accepted solution]

Hi @abhishekrws01 ,

I made a simple sample for your reference.

Sample data:

Given that the dates in this sample are incomplete. Incomplete dates are not convenient for slicer filtering. Let's create a calendar table for the slicer.

Table 2 = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

There's no relationship between tables.

Create a measure to get the sum of sales for the last 6 weeks based on the selected date.

Sales for last 6 weeks =
VAR _sel =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR _date = _sel - 42
RETURN
    CALCULATE (
        SUM ( 'Table'[Sale] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] >= _date && [Date] <= _sel )
    )

When you select a date from the slicer, the measure returns the result.

Best Regards,

Stephen Tao

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