Filter a DAX measure with dynamic date filters / Time Intelligence

[victoryamaykin]

I need to filter a calculated table to find the metric for a particular department of goods and a dynamic date range. The Sales Cycle should have a Sold % Quantity which is the total items sold divided by the number of items produced. 

These two numbers need to match: 

This DAX is not working: 

Sold % Qty Clothing = 

VAR _startSalesCycle = DATE(2024, 02, 04)

VAR _endSalesCycle = DATE(2024, 02, 27)

CALCULATE(

    [Sold % Qty],

    'Production Units by Week'[DepartmentName] = "Donated Clothing",

    DATESINPERIOD(Dates[Date], _startSalesCycle, _endSalesCycle, DAY

    ) 

)

Eventually, I want the start and end of the Sales Cycle to determine how this table gets filtered on a weekly basis. 

Which date filters should I use? 

[v-yanimei-msft]

Hi @victoryamaykin  , 

Thank you for the relevant description of your problem. Based on your description, I may not have a good understanding of your needs. I don't know what the three dates you show represent and how you'd like to filter them. Based on my understanding, I give you the following steps to achieve the desired effect.

To match the two numbers in your picture, I will show you the steps:

1.The test table Production Units by Week:

2.Modify the DAX code:

Sold % Qty Clothing =

LOOKUPVALUE('Production Units by Week'[Sold % Qty],'Production Units by Week'[DepartmentName],"Donated Clothing")

The DAX code can help you find the data what you want. Learn more about LOOKUPVALUE function (DAX) - DAX | Microsoft Learn.

3.The outcome is in the following picture.

To filter the data between Start Next Sales Cycle and End Next Sales Cycle ( I guess you want ), I will show you the steps:

1.The test table Date:

2. Right-click Production Units by Week, New Measure measure and input:

measure = 
VAR _1 = IF(SELECTEDVALUE('Date'[Date]) < MAX([Start Next Sales Cycle]) || SELECTEDVALUE('Date'[Date]) > MAX([End Next Sales Cycle]), 0, 1)
RETURN
_1

3. Right-click Production Units by Week, New Measure Sold % Qty 1 and input:

Sold % Qty 1 = IF([measure] = 1 , MAX([Sold % Qty]))

4. The outcome is in the following pictures.

You can adjust the date logic as needed to fit your scenario. Learn more about Create a relative date slicer or filter in Power BI - Power BI | Microsoft Learn.

If you have any challenges or other issues implementing this solution, can you share the specific data ( including table and model ) you have used in text and image format? In addition to this, you also need to determine if there are any specific filters or slicers applied to the report, as this may affect this calculation. This will help diagnose the problem more effectively.

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