I was go through many you-tube videos. but your Video is great and very easy to understand. therefore i was able to successfully done my Trend. thanks and Good Luck.
I was following the video and when I got to the part for creating DateRangePeriods table (around 7 minutes in) I get an error stating "The syntax for ')' is incorrect". I've compared my definition and it matches exactly what you show here. I'm not sure why I'm getting this error...
Hi, It is a fantastic a way to make the lifetime filter, however I have no idea why it is not working for me when I totally follow your step, it is keep complaining the error. shows 'the syntax for ')' is incorrect. (DAX(Var last 7 days= ADDCOLOUMNS(CALCULATETABLE('DATETABLE',DATESBETWEEN('DATETABLE'[Date ]，TODAY())),"Days range","7 days"))).' (the last 2 brackets doesn't exist on my dax, just shows by itself when it complains the error) is that because of the version difference or other reasons?
Hi @Clara, Rather then due to version difference, It is more likely because of any syntax error in typing. It should work. Please let me know if it is still not working, we can discuss on it. achieversdataengineering@gmail.com
Thank you ! If i want my chart to still show months with no data how do i do this please? eg if I select 90 days for this period and September has no data but i still want it to show on the chart as 0, How can i do this?
Question…what IF instead of Lifetime I wanted to calculate from a specific date? We relaunched our site on 5/5/22 and that would be as far back as I want to go even though we have data from before that date. So for lifetime I would want any dates from today back to 5/15/22. What Dax would you use in that case?
Hi @Sean! This will be interesting. you can follow below steps to do this. 1) Along with 30,60,90 as a new option at "custom" instead of lifetime. 2) Add a between date slicer next to 30,60,90 slicer 3) Create a measure which will return "False" if 30,60,90 is the selected value, but it should return "True" if custom is selected. 4) Use this measure as a filter for between date slicer and set it to "True" 5) Now if someone is selecting 30,60,90 then between date slicer will be disabled but when someone will click on "custom" date slicer will be enabled. 6) Now you can also apply any other filter to limit the date range shown in between date slicer. 7) Make sure to use edit interactions to allow 30,60,90 slicer to filter the between date slicer but between date slicer should not filter 30,60,90 slicer. Hope it will help!
Great video! It's the only one that helped me figure out how to accomplish this filtering. But no matter what I do, when I try to create a relationship between DateTable and DateRangePeriods I get an error saying, "A circular dependency was detected." I tried creating DateTable using CALENDARAUTO, CALENDAR, making sure "Auto date/time for new files" was turned off and trying it all again, but nothing works. Any ideas?
Glad it helped! For "A circular dependency was detected" issue, it might require a some more attention how model is setup in your PBI report. Hope you followed it step by step the way it is shown in the video.
Do you know how i would create a comparison metric using this method? For Example; i want to select 30 days, have a measure for total sales in those 30 days and a measure for total sales during the 30 days prior to that?
Hi @Andrew, Sorry for the delayed response. Please check out this video. ruclips.net/video/o1aBWfubjj8/видео.html Hope it will help. Please let me know if you need any more help on that. Thank you for watching!
Hi Suresh, could you please rephrase your question. If I understood it correctly maybe you can use a date slicer and make it a "Relative Date" slicer. Please let me know if you had a different question.
Hi @iira1311, Thank you! Please check how you have established the relationship between tables. I'm sure it will fix the issue. If not please let me know.
What if I wanna filter the data for next 30days, 60days, 90days? I just change the syntax from TODAY() -30 to TODAY()+30, but it doesn't work. Could you please help me?
Hi @Cloe, It is intresting! so you have data available for future dates? Please check the date table have future dates there too if not instead of using DAX "Calendarauto()" use Calendar(staerdate, enddate) and define the start date and end date there. Hope it will help.
![Felix "Unfair" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/Oswujxm2Ag0/mqdefault.jpg)
This was an amazing instruction and just what I was looking for!
Thank you @RSSwimmer!
I'm glad it helped.
Worked like a charm. Thank you.
Glad it helped!
@Soren Thank You for watching.
you are the man, this infomation is pure goold
Thank you @Romario!
I'm glad you liked it. 😀
I am literally working on this now and your video is a much better solution than the one I was thinking of myself. Thanks!
Hi @Sean,
I'm glad it helped.
Thank you for watching and subscribing!
Thank you very much for this amazing, simple solution to this problem. Please keep up the good work.
Thank you!
I'm glad it helped!
Excellent presentation & I used this easily in one of my projects. Thanks a lot
Thank you @pradeekb!
I'm glad it was helpful!
I was go through many you-tube videos. but your Video is great and very easy to understand. therefore i was able to successfully done my Trend. thanks and Good Luck.
Thank you @Dhanuska!
Thank you so much for your "tutorial from scratch" (= really helpful
Thank you @Philipe !
I'm glad it helped!
I was following the video and when I got to the part for creating DateRangePeriods table (around 7 minutes in) I get an error stating "The syntax for ')' is incorrect". I've compared my definition and it matches exactly what you show here. I'm not sure why I'm getting this error...
Hi Randy,
Hope it must be resolved by now. If not please do let me know I can try to help.
Thank you for watching.
Thanks a lot, you save my day
Thank you! @Lucas
Nicely done! Easy to follow along!
Thank you! @Nathan
Hi, It is a fantastic a way to make the lifetime filter, however I have no idea why it is not working for me when I totally follow your step, it is keep complaining the error. shows 'the syntax for ')' is incorrect. (DAX(Var last 7 days= ADDCOLOUMNS(CALCULATETABLE('DATETABLE',DATESBETWEEN('DATETABLE'[Date ]，TODAY())),"Days range","7 days"))).' (the last 2 brackets doesn't exist on my dax, just shows by itself when it complains the error)
is that because of the version difference or other reasons?
Hi @Clara,
Rather then due to version difference, It is more likely because of any syntax error in typing. It should work.
Please let me know if it is still not working, we can discuss on it.
achieversdataengineering@gmail.com
I am encountering this same error. Did you ever find a solution? Thanks!
thank u so much!! this worked 100% PERFECTLY! 2022
Thank You! @fitboy
Thank you ! If i want my chart to still show months with no data how do i do this please? eg if I select 90 days for this period and September has no data but i still want it to show on the chart as 0, How can i do this?
Hi @Sarah Thank you for watching.
Have you selected the option to "show items with no data". Try this for both date and data columns.
If you still have any issue post this, please drop me an email on achieversdataengineering@gmail.com. Happy to connect.
Question…what IF instead of Lifetime I wanted to calculate from a specific date? We relaunched our site on 5/5/22 and that would be as far back as I want to go even though we have data from before that date. So for lifetime I would want any dates from today back to 5/15/22. What Dax would you use in that case?
Hi @Sean!
This will be interesting. you can follow below steps to do this.
1) Along with 30,60,90 as a new option at "custom" instead of lifetime.
2) Add a between date slicer next to 30,60,90 slicer
3) Create a measure which will return "False" if 30,60,90 is the selected value, but it should return "True" if custom is selected.
4) Use this measure as a filter for between date slicer and set it to "True"
5) Now if someone is selecting 30,60,90 then between date slicer will be disabled but when someone will click on "custom" date slicer will be enabled.
6) Now you can also apply any other filter to limit the date range shown in between date slicer.
7) Make sure to use edit interactions to allow 30,60,90 slicer to filter the between date slicer but between date slicer should not filter 30,60,90 slicer.
Hope it will help!
This helped! Thank you!
I'm glad it helped!
Thank you for watching.
Thank you so much
You're most welcome.
I'm glad it helped.
Great video! It's the only one that helped me figure out how to accomplish this filtering. But no matter what I do, when I try to create a relationship between DateTable and DateRangePeriods I get an error saying, "A circular dependency was detected." I tried creating DateTable using CALENDARAUTO, CALENDAR, making sure "Auto date/time for new files" was turned off and trying it all again, but nothing works. Any ideas?
Glad it helped! For "A circular dependency was detected" issue, it might require a some more attention how model is setup in your PBI report. Hope you followed it step by step the way it is shown in the video.
Do you know how i would create a comparison metric using this method? For Example; i want to select 30 days, have a measure for total sales in those 30 days and a measure for total sales during the 30 days prior to that?
Hi @Andrew, Sorry for the delayed response. Please check out this video.
ruclips.net/video/o1aBWfubjj8/видео.html
Hope it will help. Please let me know if you need any more help on that.
Thank you for watching!
Sir I need 30 60 90 days sale chart when I select month or date in the date and month selection how to do it.
Hi Suresh, could you please rephrase your question. If I understood it correctly maybe you can use a date slicer and make it a "Relative Date" slicer. Please let me know if you had a different question.
Very useful Sir
Thank you Ashok!
amazing sir... keep making for us please
Thank you Gopal. It is motivating.Please subscribe my channel to receive alerts on any latest videos which I upload.
I have implemented this solution. Thanks for that. However, no data is reflecting when selecting from slicer. Could you pls assist
Hi @iira1311,
Thank you! Please check how you have established the relationship between tables. I'm sure it will fix the issue. If not please let me know.
Thank you man
I'm glad you liked it.
Thank you for watching.
instead of datesbetween can we datesinperiod !
Yes, we can use that as long as it is solving the issue.
Great keep it up
Thank you Aslam ji!
What if I wanna filter the data for next 30days, 60days, 90days? I just change the syntax from TODAY() -30 to TODAY()+30, but it doesn't work. Could you please help me?
Hi @Cloe, It is intresting! so you have data available for future dates?
Please check the date table have future dates there too if not instead of using DAX "Calendarauto()" use Calendar(staerdate, enddate) and define the start date and end date there.
Hope it will help.