Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
@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.
@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!!
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.
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!!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |