Forum Discussion

westman's avatar
westman
Regular Visitor
2 years ago

Selected value in Date range slicer using Between on measures in a card visual

Hello-

I am trying to have a between date range slicer  filter a measure that filters a table 
Here is the original Measure :

Current Open Orders = CALCULATE([SalesPeriodT],FILTER(MTDSalestbl, MTDSalestbl[Sequence]= "2T-Current Open Orders Grand Total"))
 
Here is the SalesPeriodT Measure nested in the the above measure:
SalesPeriodT = Sumx(MTDSalestbl, MTDSalestbl[Amount]
 
Here is the measure i tried to write to have the between date slicer filter the above measure:
Test Open Orders =
CALCULATE (
    SUMX (
        FILTER (
            MTDSalestbl,
            MTDSalestbl[Sequence] = "2T-Current Open Orders Grand Total"
        ),
        Measuretbl[SalesPeriodT]
    ),
    FILTER (
        ALLSELECTED ( 'Datestbl'[Date] ),
        'Datestbl'[Date] >= MIN ( 'Datestbl'[Date] )
            && 'Datestbl'[Date] <= MAX ( 'Datestbl'[Date] )
    )
)

When i try to add the date slicer it just blanks all the cards to 2 dashes and my other donut chart to empty
Attached is a screen shot of my report and its relationships
            

 

 
Any advice would be appreciated

 

 

Need Help

4 Replies

  • westman's avatar
    westman
    Regular Visitor
    2 years ago

    Side note: The measures  Current Open Orders and SalesPeriodT work if I  remove the date slicer, only adding the date slicer does it result in the picture above

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi westman ,
    Based on your description, I created the following test data
    Datestbl

    MTDSalestbl

    Relationship

    You could try changing ALLSELECTED ( 'Datestbl'[Date] ) to ALL( 'Datestbl'[Date] )

    Test Open Orders = 
CALCULATE (
    SUMX (
        FILTER (
            MTDSalestbl,
            MTDSalestbl[Sequence] = "2T-Current Open Orders Grand Total"
        ),
        MTDSalestbl[SalesPeriodT]
    ),
    FILTER (
        ALL ( 'Datestbl'[Date] ),
        'Datestbl'[Date] >= MIN ( 'Datestbl'[Date] )
            && 'Datestbl'[Date] <= MAX ( 'Datestbl'[Date] )
    )
)

    Final output

    Best regards,

    Albert He

     

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

     

     

     

     

    • westman's avatar
      westman
      Regular Visitor
      2 years ago

      Thank you for replying Albert, i made your suggestion change of ALL vs ALLSELECTED.  However, there was no change in the output into the card visual.  Same 2 dashed lines.   


      I am wondering if using a Min Date and Max date as a VAR in the measure may be more accurate way to write the measure instead of using FILTER.  What are your thoughts?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi westman ,
        Thank you for your reply, the above dax function is based on the sample data I created, you can try to use the maximum date or minimum date as the filter condition, if you want more accurate help, please provide the detailed sample data and hide the sensitive information.

        Best regards,

        Albert He

         

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