Dynamic date Measure from slicer and use the measure value to compare with a column

[Hemanth96]

Hi, 

I have a 2 measures namely - Week start date and Week end date. These two measures are used to compare the date from another column called Purchased date (sample data shown below and return 1,0 in a new column called Purchased_Week_Flag. 

Purchased date

11/20/2022

11/21/2022

11/23/2022

11/23/2022

11/27/2022

11/28/2022

11/30/2022

12/01/2022

12/02/2022

12/02/2022

DAX for Purchased_Week_Flag : 

Purchased_Week_Flag = IF('Table'[Purchased date].[Date] >= [Week start date] && 'Table'[Purchased date].[Date] <= [Week end date],1,0). 

Then I calculate the sum of Purchased_Week_Flag and display it as Total purchases in week. 

Problem Statement: Currently I'm manually entering the date values in Week start date and Week end date using DATE() function , but I want to make the measures take the date values from Calendar slicer, which should take the relative date range as Week start date and Week end date. And then the measures will be used in Purchased_Week_Flag. 

From couple of solutions I found in the forums, I used the following DAX in the measure to take value from calendar slicer with below DAX

Week start date = CALCULATE(Min('Calendar'[Date]),ALLSELECTED('Calendar'))

Week end date = CALCULATE(Max('Calendar'[Date]),ALLSELECTED('Calendar'))

So, If i select the slicer date range as 11/27/2022 to 12/03/2022, I should be seeing 6 flags as 1 and rest flags as 0.

- but all my Purchased_Week_Flag is showing as 1. 

Please help as I do not want to manually enter the dates everyweek and publish a new report everyweek.

[Anonymous · Accepted solution]

Hi @Hemanth96 ,

According to your statement, I think your issue should be caused that you want to catch date dynamicly from slicer in calculated column.

As far as I know, Power BI doesn't support us to get date dynamicly from slicer. [Week start date] will return the min date in slicer table and [Week end date] will return the max date in slicer table.

That's the reason that your code works well when you use Date() to enter date manually and when you get date from slicer, your calculate column will only return 1.

If you want to do this, I suggest you to create a measure. 

Purchased_Week_Flag =
IF (
    SELECTEDVALUE ( 'Table'[Purchased date] ) >= [Week start date]
        && SELECTEDVALUE ( 'Table'[Purchased date] ) <= [Week end date],
    1,
    0
)

Best Regards,
Rico Zhou

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

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship (Many to One and Single) from the Purchase date column of the Purchase table to the Date column of the Calendar Table.  Create a slicer from the Calendar Table and select any date range.  Write his measure

Purchases = countrows(Data)

Hope this helps.

[Hemanth96]

Thanks Ashish. 

From your solution, I built relationship from Purchase date to Calendar date and its coming as expected, but I have other metrics to be calculated in similar way namely payment date, delivered date, invoiced date. 

I built relationship (many to one) for the payment date, delivered date and invoiced date to Calendar date, but the metrics aren't giving the result as expected. 

Is there any way where I can put a date (from inputting through text or selecting calendar), and that date value should be taken into the Week start date? Since the slicer is filtering out my data and I tried to remove the interactions with my cards, but still the results aren't accurate. 

[Hemanth96]

Thanks Ashish. 

From your solution, I built relationship from Purchase date to Calendar date and its coming as expected, but I have other metrics to be calculated in similar way namely payment date, delivered date, invoiced date. 

I built relationship (many to one) for the payment date, delivered date and invoiced date to Calendar date, but the metrics aren't giving the result as expected. 

Is there any way where I can put a date (from inputting through text or selecting calendar), and that date value should be taken into the Week start date? Since the slicer is filtering out my data and I tried to remove the interactions with my cards, but still the results aren't accurate. 

[Ashish_Mathur]

You are welcome.  The other relationships should be Many to One and Single and Inactive.  The new measure will be:

Measure1 = calculate(countrow(Data),useelationship(Data[Payment date],calendar[date]))

Write similar measures for others as well.

Hope this helps.