Forum Discussion

finnprice's avatar
finnprice
Regular Visitor
1 year ago
Solved

Create a Date Slicer to Influence Calculated Measures

Hello All,

I hope you are having a great day!

 

I am looking to get some help in setting up a dynamic date slicer that can influence multiple different calculated measures. Where this gets tricky, the calculated measures are all referencing different date hierarchies (in the example below one measure references "closed date" compared to the other that references "created date").

 

My original thought was to create a "master date" table and link it to the existing date columns but quickly realized this would create a cascade of issues elsewhere. I have since started looking into creating a master date parameter instead, but am getting stuck on the execution part.

 

Current State

Visuals reference the following calculated measures:

  • $ New Orders YTD'25 = CALCULATE(SUM(Opportunity[Amount]),DATESBETWEEN(Opportunity[CloseDate],DATE(2025,1,1),DATE(2025,4,14)),FILTER(Opportunity,Opportunity[StageName]="Closed Shipped" ||Opportunity[StageName]="Closed Won" ||Opportunity[StageName]="Released to Production" ||Opportunity[StageName]="Shipped"))+0
  • $ New Opportunities YTD'25 = CALCULATE(SUM(Opportunity[Amount]),DATESBETWEEN(Opportunity[CreatedDate],DATE(2025,1,1),DATE(2025,4,14)))

 

As you can tell, when the date range changes I need to go into the DAX formula and manually change the dates.

 

Target State

Slicer that allows you to set start date and end date

  • $ New Orders YTD'25 = CALCULATE(SUM(Opportunity[Amount]),DATESBETWEEN(Opportunity[CloseDate],START DATE FROM SLICER,END DATE FROM SLICER,FILTER(Opportunity,Opportunity[StageName]="Closed Shipped" ||Opportunity[StageName]="Closed Won" ||Opportunity[StageName]="Released to Production" ||Opportunity[StageName]="Shipped"))+0
  • $ New Opportunities YTD'25 = CALCULATE(SUM(Opportunity[Amount]),DATESBETWEEN(Opportunity[CreatedDate],STARTDATE FROM SLICER,END DATE FROM SLICER))
I am open to other suggestions if you think I can get this functionality a different way! 
 
Thanks for your assistance.
 
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi finnprice

    It’s important to clarify to the head of ERP that certain functionalities are essential and should be used as intended. Field parameters are designed to switch between dimensions/measures, not to combine or compare them in the way you're trying to achieve.

     

    For example, in your table, Row 1 has an Order Date of February 1 and a Shipment Date of February 2. If you use a field parameter to switch the filter to Shipment Date, the row will still be included—because the Shipment Date is February 2—even though the Order Date is February 1. This means the filter only respects the selected dimension (Shipment Date in this case), and not the Order Date.

     

    Below is a workaround

    completed based on created date = 
CALCULATE (
    SUM ( Data[Value] ),
    FILTER ( ALL ( Data ), Data[Completed Date] IN VALUES ( Data[Created Date] ) )
)

    In the above formula, the Created Date has been added to the visual, and it currently returns rows where the Completed Date matches the Created Date. However, this approach has limitations. Once additional dimensions are added to the visual, you may start seeing unexpected results.

     

    Additionally, this setup only includes Completed Dates that also exist in the Created Date column. So, for example, if October 2 appears in the Completed Date but not in the Created Date, it will be excluded from the results.

     

    PS: you can create dates table without importing an external data with M or DAX.

4 Replies

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    Hi finnprice ,
    Thanks for reaching out to Microsoft Fabric Community.

     

    The approach suggested by the danextian  is appropriate for the use case you described. When your measures rely on different date columns, such as CloseDate and CreatedDate, the most reliable way to control them using a single date slicer is by introducing a Date table and managing relationships through DAX using the USERELATIONSHIP function.

     

    While parameters are useful in certain situations, they are not designed to filter multiple date columns at once. Using them in this context would not achieve the intended outcome.

     

    That said, if adding a new table is a concern, it's worth noting that the Date table can be created directly in Power BI using DAX, without importing data from external sources. This allows the model to stay self-contained while enabling consistent filtering logic.

     

    Hope this helps. Please feel free to reach out if you have any further questions.

    Please consider marking the helpful reply as Accepted Solution to assist others with similar issues.

    Thank you.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi finnprice 

    You can definitely use the same dates table to create multiple relationships with a single fact table, but only one relationship can be active at a time—that’s the one used for direct filtering. If you need to use an inactive relationship, you can still do so within a measure by using the USERELATIONSHIP function. So based on your formula, and assuming the relationship between CloseDate and DatesTable[Date] is inactive, here’s how your measure would look:

    $ New Orders YTD'25 =
CALCULATE (
    SUM ( Opportunity[Amount] ),
    USERELATIONSHIP ( datestable[date], Opportunity[CloseDate] ),
    KEEPFILTERS ( Opportunity[StageName]
        IN { "Closed Shipped", "Closed Won", "Released to Production", "Shipped" } )
) + 0

    Note: i changed your filter. It is not a good practice to be filtering a table when you simply need to filter a column.

     

    Please see attached sample pbix on the use of USERELATIONSHIP.

    • finnprice's avatar
      finnprice
      Regular Visitor
      1 year ago

      Hi danextian ,

      Thanks for taking the time to help!


      Your solution looks like a great option when you have a master date table, however the head of ERP does not want me to create additional tables in this workspace. Instead, he is hoping I can find a solution using parameters - do you think there is a way to create this functionality using parameters?

       

      P.s. thank you for cleaning up my filter syntax, still learning DAX so all best-practice revisions are welcome!

      • danextian's avatar
        danextian
        Super User
        1 year ago

        Hi finnprice

        It’s important to clarify to the head of ERP that certain functionalities are essential and should be used as intended. Field parameters are designed to switch between dimensions/measures, not to combine or compare them in the way you're trying to achieve.

         

        For example, in your table, Row 1 has an Order Date of February 1 and a Shipment Date of February 2. If you use a field parameter to switch the filter to Shipment Date, the row will still be included—because the Shipment Date is February 2—even though the Order Date is February 1. This means the filter only respects the selected dimension (Shipment Date in this case), and not the Order Date.

         

        Below is a workaround

        completed based on created date = 
CALCULATE (
    SUM ( Data[Value] ),
    FILTER ( ALL ( Data ), Data[Completed Date] IN VALUES ( Data[Created Date] ) )
)

        In the above formula, the Created Date has been added to the visual, and it currently returns rows where the Completed Date matches the Created Date. However, this approach has limitations. Once additional dimensions are added to the visual, you may start seeing unexpected results.

         

        Additionally, this setup only includes Completed Dates that also exist in the Created Date column. So, for example, if October 2 appears in the Completed Date but not in the Created Date, it will be excluded from the results.

         

        PS: you can create dates table without importing an external data with M or DAX.