Forum Discussion

AMitchell's avatar
AMitchell
Frequent Visitor
1 year ago

Date Selection

Hi all

 

I have data encompassing 4 different dates which i need to be able to compare. The only slicer option that seems to allow this is this one 

Is there a way to just show my relevant dates within this rather than every day being able to be selected? I only have one date per year.

Need Help

5 Replies

  • freginier's avatar
    freginier
    Solution Sage
    1 year ago

    Hey there!

     

    You can create a new table in Power BI with only the specific dates you need (such as the four dates you want to compare). Then, use this table for your slicer.

     

    1. Create a custom date table with only the dates you want to use. For example, if you have four dates (e.g., 25/12/2021, 25/12/2022, 25/12/2023, 25/12/2024), you can create a table like this:

    RelevantDates =
    DATATABLE(
    "ValuationDate", DATATYPE DATE,
    {
    {DATE(2021,12,25)},
    {DATE(2022,12,25)},
    {DATE(2023,12,25)},
    {DATE(2024,12,25)}
    }
    )

    - Then, add this table to your slicer and use it to filter the relevant dates.

     

    2. Instead of using a continuous date slicer, you can use a Dropdown slicer (or "List slicer") that only displays the four specific dates you're interested in. This way, the user can select from only the four dates rather than every day.

     

    3. If you need the slicer to work dynamically with dates relative to today, you can use the Relative Date Slicer. However, this is generally for selecting relative time periods (e.g., last 30 days) and may not be applicable for your specific case.

     

    Hope this helps!

    😁😁

  • AMitchell's avatar
    AMitchell
    Frequent Visitor
    1 year ago

    This was really helpful but i'm not quite getting what i need. What I would really like is for the top slicer to contriol the monetary card below and the second slicer to select a date for the comparison percentage card. Do you know if this is possible in PowerBi? 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi AMitchell 

       

      Thanks for the reply from freginier .

       

      If I understand correctly, you want the slicer above to affect the card on the left, and then compare it to the value of the date selected in the slicer below, presented in the card on the right, right?

       

      If so, the following test is for your reference:

       

      My sample:

       

       

      Create two calculated tables as slicers.

       

       

      You can change the slicer style to “Vertical list”.

       

      Create two measures as follow

      sum = CALCULATE(SUM('Table'[values]), FILTER('Table', [Date] = SELECTEDVALUE(Slicer2[Date])))

       

       

      Measure = 
VAR _sum = CALCULATE(SUM('Table'[values]), FILTER('Table', [Date] = SELECTEDVALUE(Slicer1[Date])))
RETURN
DIVIDE(_sum, [sum])

       

      Output:

       

      Best Regards,
      Yulia Xu

       

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

  • AMitchell's avatar
    AMitchell
    Frequent Visitor
    1 year ago

    Thank you for this. For the values do they need to be hardcoded or can they be dynamic?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi AMitchell 

       

      Do you mean that values are calculated? Is it a measure? Then you can try to change the two formulas to the following.

      Measure = 
VAR _sum = CALCULATE([YourMeasureName], FILTER('Table', [Date] = SELECTEDVALUE(Slicer1[Date])))
RETURN
DIVIDE(_sum, [sum])

       

      sum = CALCULATE([YourMeasureName], FILTER('Table', [Date] = SELECTEDVALUE(Slicer2[Date])))

       

      I hope this helps.

       

      If you need further help, could you provide some example data and the logic of calculating values so that I can better reproduce your scenario? How to provide sample data in the Power BI Forum - Microsoft Fabric Community Please remove any sensitive data in advance.

       

      Best Regards,
      Yulia Xu

       

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