2 date ranges for data

[reh169]

I am currently working with a large data set and I am using created date as the driver for the majority of my reports. Currently created date spans from 01/01/2016 to current day but will be including much more history soon.  The way I have the data set up wtih measures now is to look at this year and last year based on the year of the creation date. Now I am being asked to be able to filter the data to one set of time and compare the second set to that first one. Meaning If they set the first set to 1/1/2016 to 5/1/2016 they want to compare it second set of data on the same graph of 1/1/2017 to 5/1/2017. So what I did was create a copy of the created date and give them seperate names and update the measures to look at the different date sets and then  use those as my filters to adjust the data. So Created date 1 and Created date 2 on the same graph for time frames that are the same months but for different years. When I did this and updated the measures that the graph is based on the graphs went blank. My data set is to large and diverse to create a key to link it to a duplicate of itself and I am not out ideas. I tried to have a year and a quarter filter but was told that was not ideal for what was wanted being that they want more flexability in being able to pick date ranges to compare.

[MFelix]

Hi @reh169,

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

[reh169]

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.

[MFelix]

HI @reh169,

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

[reh169]

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]

@reh169,

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

[reh169]

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?

[MFelix]

Hi @reh169,

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

[reh169]

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

[MFelix]

Hi @reh169,

Can you please elaborate on what type of calculations you are talking maybe give an example.

Regards,

MFelix

[reh169]

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.

[AlbertoFerrari]

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.

[reh169]

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]

@reh169.

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

[reh169]

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.

[AlbertoFerrari]

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

[TomMartens]

Hey @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

[AlbertoFerrari]

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!

[MFelix]

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

[Anonymous]

@reh169,

Please help to share sample data of your table and post DAX you currently use.

Regards,
Lydia

[reh169]

AxisCurrent 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?