Forum Discussion
2 date ranges for data
What I currently have is for last year and this year, but my date range is going to be expanding back by a few years and what I am being asked to do is to have 2 date filters for the graphs so that they can compare any 2 time periods they want. I created the calendar table and I thinked it to my property table with the date field to both of my create dates...should I have put 2 date fields in the calendar table one for each create date I am wanting to filter on?
Hi Anonymous,
I have made this tests and it works,
My assumptions are 1 table sales (Date + Sales Amount) + 2 Calendar tables (Calendar and Calendar_Sec) and no relationships between all tables.
Create this measures:
Total_Sales = SUM(Sales[Sales])
Sales_CALENDAR =
CALCULATE (
[Total_Sales];
DATESBETWEEN (
Sales[Date];
MIN ( 'Calendar'[Date] );
MAX ( 'Calendar'[Date] )
)
)
Sales_CALENDAR_SEC =
CALCULATE (
[Total_Sales];
DATESBETWEEN (
Sales[Date];
MIN ( 'Calendar_Sec'[Date] );
MAX ( 'Calendar_Sec'[Date] )
)
)Then add two slicers for each of the calendars and the Date from the sales table in your x-axis should get what you want.
Here is the view with month only.
Here is a PBIX file to test.
Regards,
MFelix
Hi,
You can get much better performance if you actually create the two relationships as inactive ones, and then you enable them on demand in the measure, using a slight variation of your code. This is the model:
And, with the model in place, you author the measures in this way:
Sales_CALENDAR = CALCULATE ( [Total_Sales], USERELATIONSHIP( 'Calendar'[Date], Sales[Date] ) ) Sales_CALENDAR_SEC = CALCULATE ( [Total_Sales], USERELATIONSHIP( Calendar_Sec[Date], Sales[Date] ) )By leveraging relationships, you obtain much better performance, since you are using Storage Engine instead of Formula Engine. You need a few hundre millions rows to notice the difference, yet is is worth optimizing the model from the beginning. Besides, the code is a bit cleaner.
I wish I knew how to attach a PBIX here, but I am just learning how to answer, attaching a file looks too hard for me :)
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.comHey AlbertoFerrari
you can't attach files, just embed screenshots, if you want to share a file you have to share a link that directs to the file.
Regards
Ouch... such a pity! 90% of the answers would be so much easier if we could share PBIX files here... anyway, thanks for confirming I am not too old not to see where the "share a file" button was hidden, I was searching for it everywhere!
Hi AlbertoFerrari,
What I usually do is to add a Wetranfers link but there are other persons that do it on the dropbox/google drive.
On an additional note the add file option is available when you make an issue post or a data story (is also treated as an issue).
Regards,
MFelix
MFelix, this worked for me but I also need to be able to use it to create measures like taking my total amount and dividing by number of business hours for each time frame but I am not having any luck...thoughts?day of week normalized by business hours
Hi Anonymous,
Can you please elaborate on what type of calculations you are talking maybe give an example.
Regards,
MFelix
Sure, that is my biggest downfall when asking for help...I get asked to elaborate a lot. So I am summing guest cards and I am showing by day of the week but I have divide the sum by the business hours that we were open so that days that when we are open for fewer hours I can see the sum of guest normalized. I was doing the comparison last year to this year and this is what my calcation looked like. GC Calendar / Average of Business_Hours = DIVIDE( SUM('Property'[GC Last Year]), AVERAGE 'Property'[Business_Hours])) GC Last Year being a measure I created that looked at the year of the created date and only counted the guest cards if they met that criteria. I tried to replace the GC Last Year piece with the GC Calendar piece and I got an error.
Sure, that is my biggest downfall when asking for help...I get asked to elaborate a lot. So I am summing guest cards and I am showing by day of the week but I have divide the sum by the business hours that we were open so that days that when we are open for fewer hours I can see the sum of guest normalized. I was doing the comparison last year to this year and this is what my calcation looked like. GC Calendar / Average of Business_Hours =
DIVIDE( SUM('Property'[GC Last Year]), AVERAGE 'Property'[Business_Hours])) GC Last Year being a measure I created that looked at the year of the created date and only counted the guest cards if they met that criteria. I tried to replace the GC Last Year piece with the GC Calendar piece and I got an error.
If "GC Last Year" is a measure, you cannot SUM it, you only need to reference it. My guess is that you are just making confusion between measures and columns.
If it is a measure, you should call it this way:
DIVIDE( [GC Last Year], AVERAGE 'Property'[Business_Hours]) )Then, you can replace it with any other measure.
Moreover, I am not 100% sure, but that AVERAGE looks wrong to me... why do you AVERAGE hours? The calculation might not be accurate. if not wrong.
I am averging business hours because each location is open the same amount of hours for each day of the week Monday through Friday is 8 hours, Saturday is 7 hours and Sunday is 4 hours, by dividing the total number of guest cards by the number of hours we were open it normalizes the counts of guest cards so that Sunday and Saturday are more in line with the rest of the week even though they were open for fewer hours. Your suggestion on removing the sum worked! Thank you so much!
Anonymous.
Glad to hear the issue is solved. Please mark appropriate replies as solutions, that way, other community members would easily find the answer when they get same issues.
Regards,
Lydia
