Forum Discussion
2 date ranges for data
Hi MFelix,
I added the calendar table and I linked it to my created date subjet and created date compare to it and put it as the x axis on my graph but my graph is still empty. I am probably missing something small. How can I sent you a sample of the data? I will try to explain better. I have a created date which tells me the day which a guest card was created. I am trying to look at that over 2 time frames and compare them. So I duplicated the column in my query and called one created date subject and the other called create date compare. I have a filter for each so that you can compare a 2 different time periods to one another.
Anonymous,
Create relationship between the create date filed of your original table and date field of your calendar table, then create the following similar measures in your original table.
total sales = SUM(Table[SalesAmount])
next year sales = CALCULATE([total sales],DATEADD('Calendar'[DateKey],+1,YEAR))
last year sales = CALCULATE([total sales],DATEADD('Calendar'[DateKey],-1,YEAR))
After that, create a visual using date field of calendar table and the above measures, create slicer using date field/month field/year field, and use slicer to filter the visual.
If the above steps don't help, please upload your sample data to OneDrive and post shared link here.
Regards,
Lydia Zhang
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?
Anonymous,
Please help to share sample data of your table and post DAX you currently use.
Regards,
LydiaAxisCurrent View with no dataHere are some pictures. filtersmappingHow I want it to work using created date filters and how it is currently working showing year over yearHow do I show the data set?
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.com
