Forum Discussion

DuaneF's avatar
DuaneF
New Member
1 year ago
Solved

Using Slicer end date in Measure

I'm new to Power BI, and still learning. I have created a measure to determine the last selected date from my date slicer. I want to use this date to determine the date that must be used in a sum query. The measure keeps ons selecting all the date ranges, and not just the end date of the slicer. Please can someone help me

 

Total_SOH_Selected_Date =
calculate(
    sum(Artikelbestand[Cost_Total]),
    Filter(
        allselected(Artikelbestand[Trans_Date]),
        'Measure'[Last Selected Date])
    )
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi DuaneF  Try this:

    Total_SOH_Selected_Date = 
CALCULATE(
    SUM(Artikelbestand[Cost_Total]),
    FILTER(
        ALLSELECTED(Artikelbestand),
        Artikelbestand[Trans_Date] = [LastSelectedDate]
    )
)

     

     

    Hope this helps!!

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

     

    Best Regards,

    Shahariar Hafiz

2 Replies

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

    Hi DuaneF  Try this:

    Total_SOH_Selected_Date = 
CALCULATE(
    SUM(Artikelbestand[Cost_Total]),
    FILTER(
        ALLSELECTED(Artikelbestand),
        Artikelbestand[Trans_Date] = [LastSelectedDate]
    )
)

     

     

    Hope this helps!!

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

     

    Best Regards,

    Shahariar Hafiz

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

    To make your measure consider only the end date selected in the slicer, you’ll want to reference the last date in the selection range. You can use `MAX` or `LASTDATE` functions to capture the end date from the slicer and then apply it in your calculation. Here’s how you can modify your measure:

    DAX

     

    Last_Selected_Date =
MAX('Date'[Trans_Date]) // assuming you are using a date table with 'Date' as the table name and Trans_Date as the date column.

Total_SOH_Selected_Date =
CALCULATE(
SUM(Artikelbestand[Cost_Total]),
Artikelbestand[Trans_Date] = [Last_Selected_Date]
)

     



    Explanation:
    1. `Last_Selected_Date` captures the end date from the slicer (the maximum date selected).
    2. `Total_SOH_Selected_Date` then uses this date to filter `Artikelbestand[Trans_Date]` to only the end date of your selection, summing `Cost_Total` accordingly.

     

    This should make the measure consider only the end date in your slicer. Let me know if you encounter any issues!

     

    Did I answer your query ? Please mark this as solution . Appreciate your Kudos 🙂