Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
myndscaper
Frequent Visitor

DATESBETWEEN VS DATEADD - Multi Year Line Chart

Hi all,

 

I am trying to create a multi year line chart with calendar months on x-axis and percent on y-axis. I have a date table and fact table in my model. To create each years data, I am using the same measure ([HitRate]) with a slightly different date filter inside of CALCULATE. For example:

 

When I use DATESBETWEEN, I get the same total for all months....

HitRate2017 = 
CALCULATE(
       [HitRate],
       DATESBETWEEN(
            'Calendar'[Dates],
             DATE(2017,01,01),
             DATE(2017,12,31)
       )
)

If I instead use DATEADD instead, I get a unique value for each month, like I want.

HitRate2017 = 
CALCULATE( 
       [HitRate], 
       DATEADD(
           'Calendar'[Dates],
           -2,
           YEAR
       )
)
           

Can someone help me understand why I am getting different results? I suspect it has to do with the difference between these two functions with repsect to filter/row context, but I'm not quite experienced enough with Dax to understand why. Also, there may be a better, more dynamic way to accomplish my goal without either function. I would certainly appreciate feedback on that as well, but at this point I really want to understand why it doesn't work, since I think it will help me understand Dax better.

 

Thanks!

 

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@myndscaper in following measure you have given the fixed value to calculate Datesbetween, so every date on X axis is returing the same value because datesbetween is not dynamic, whereas other measure, it is dynamic because it is shifting dates back to 2 years. Hope it helps. 

 

If you pass dynamic date to your datesbetween, you will get the same result as other measure.

 

HitRate2017 = 
CALCULATE(
       [HitRate],
       DATESBETWEEN(
            'Calendar'[Dates],
             DATE(2017,01,01),
             DATE(2017,12,31)
       )
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@myndscaper in following measure you have given the fixed value to calculate Datesbetween, so every date on X axis is returing the same value because datesbetween is not dynamic, whereas other measure, it is dynamic because it is shifting dates back to 2 years. Hope it helps. 

 

If you pass dynamic date to your datesbetween, you will get the same result as other measure.

 

HitRate2017 = 
CALCULATE(
       [HitRate],
       DATESBETWEEN(
            'Calendar'[Dates],
             DATE(2017,01,01),
             DATE(2017,12,31)
       )
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you @parry2k . I think I'm stll a little confused. I thought the date range specified in DATESBETWEEN would act some what like a sql where clause, limiting the range of possible dates, and then when aplied to a pivot table (matrix) or chart, would still filter by the specfic month.  Is there a different syntax I would use for something like this? Could I just filter with something like 'calendar'[dates] = 2017? Would that filter the chart to just 2017 data but still display individual month values?

 

Thanks for your help!!

Let me ask you this before I answer your question. What are you trying to achieve. Could be totally different solution.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

HitRateExample.png

 

Very fair question!

Immediate need is a line graph displaying multple years data by caledar month, like above. Based on your feedback, I have that completed. Less immediate, I want to understand why the date range specified in DATESBETWEEN results in a grand total of all of the dates values instead of being slicable on Month on a table or graph--just really confused by what Dax is doing under the hood.

 

Very much appreciate your help, and completely understand if this is a little too broad of a question to address.

 

 

 

@myndscaper there is great post most recently posted by Matt Alington and that will explain  how things works under the hood. Here is the link to that blog



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks, @parry2k !! I'm a frequent follower of  @MattAllington's blog, but had not seen this one yet. Thank you!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.