How to format a date range within a measure

DAX DAX Calculations
1

Hello,

I’m trying to create a fixed date range within a measure in order to calculate the average revenue per lead for dates after 2018.

Here’s what I have so far:

Average Revenue Per lead after 2018 = CALCULATE ( [Revenue Per Lead], DimLeadDate[CalendarDate] > 1/1/2018 )

However this is not producing the correct result.

I want this measure to only capture data after 2018, no matter the filter in place. Even if the user filters the dates with a slicer for after 2019, I still want the measure to capture lead info from 2018 onward such that this revenue per lead number remains consistent.

I thought I might need to use the ALL function to ensure the slicers don’t impact the value, but I’m not sure how to use ALL in conjunction with selecting dates after 2018. They seem to contradict one another.

Thanks for your help!

2

Hi @pete.langlois,

You are correct in assuming you will need to incorporate the ALL function but also FILTER.

    Average Revenue Per lead after 2018 = 
    CALCULATE ( [Revenue Per Lead], 
        FILTER ( ALL(DimLeadDate),
            DimLeadDate[CalendarDate] >= DATE(2018,1,1 )
        )
    )

You can use the Learning map to select which courses to start working through within the portal.

I hope this is helpful

1 Like
3

Thank you!

4

Hi @pete.langlois, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!