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 calenda...
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.           

     

     

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.