Forum Discussion

Netope's avatar
Netope
Icon for Helper I rankHelper I
1 year ago
Solved

Inject date selection from a dropdown slicer into a dax formula

Hi I am trying to create a measure that sums Totals for all contracts with EndDate >= the Year/month selected in a slicer dropdown.

 

See PBI File with additonal explanations.

 

https://drive.google.com/file/d/1Mb4s79kyTZAUg5WK7EmrK6W7MpGwA4uX/view?usp=sharing 

 

Thanks!

DAX
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Netope ,

     

    You need to create an unrelated dimension table, for which you can view the following results:

    Table = SUMMARIZE('Selected Value',[EndDate])

Total = var _s = SELECTEDVALUE('Table'[EndDate])
RETURN CALCULATE(SUM('Selected Value'[Act]),FILTER('Selected Value',[EndDate]>=_s))

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Netope ,

     

    You need to create an unrelated dimension table, for which you can view the following results:

    Table = SUMMARIZE('Selected Value',[EndDate])

Total = var _s = SELECTEDVALUE('Table'[EndDate])
RETURN CALCULATE(SUM('Selected Value'[Act]),FILTER('Selected Value',[EndDate]>=_s))

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

    • Netope's avatar
      Netope
      Icon for Helper I rankHelper I
      1 year ago

      Thanks! it worked just fine

  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    1 year ago

    Hi Netope  Try the below code:

    Total = 
VAR _selectedDate = SELECTEDVALUE('Selected Value'[EndDate])
RETURN
    CALCULATE(
        SUM('Selected Value'[Act]),
        FILTER(
            ALLEXCEPT('Selected Value', 'Selected Value'[Contract]),
            'Selected Value'[EndDate] >= _selectedDate
        )
    )

     

    Hope this helps!!
    If this solved your problem, please accept it as a solution and a kudos!!

     

    Best Regards,
    Shahariar Hafiz

    • Netope's avatar
      Netope
      Icon for Helper I rankHelper I
      1 year ago

      Hi,

       

      Thanks for your reply, I added the formular you suggested but the ourtcome reamins the same, the table only shown contract with end deate in the seclected month.

      Any addiotnal ideas?

      Thanks!