Forum Discussion
2 date ranges for data
Hi Anonymous,
Not sure if i'm understanding your question correctly but if you add a calendar table to link to your data and use that calendar for the axis of your visuals this should give you what you need when calculating your measures.
Regards,
MFelix
I added a calendar table do I link that to my created date? Here is my issue, I have data from 1/1/2016 to yesterday. I have a graph that lists the months of the year and one column is for this year and one column is for last year so you can compare them. So for whaterver time frame I select in 2016 I want to see how the numbers compare to 2017. Are you saying that by linking my created date to the caldenar I can do this? I have not used the calendar before and just created it so I am little confused as to how to use it.
HI Anonymous,
You need to link the calendar table to the column that you are using the measures and then replace both the visual axis and in the measure the date by the calendar date.
If you can give some sample of the data and measure I could send you a walktrough.
Regards,
MFelix
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