Forum Discussion

abhishekrws01's avatar
abhishekrws01
Helper I
3 years ago
Solved

Selectedvalue Function returning Null Values

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.
 
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.           

     

     

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    3 years ago

    Slicers cannot affect calculated columns or tables.

    Slicers are for visuals and measures.

    • abhishekrws01's avatar
      abhishekrws01
      Helper I
      3 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.