Performance vs Comparison Date Range using DateTime

[Anonymous]

DATA

I have a report with three seperate tables:  'Performance', 'Calendar' and 'Offset'.  There is a '1 to many' reationship between the 'Date' fields on the Calendar and Performance tables.

Here is an example of my data tables:
Performance

Date	Product Name	Traffic (web traffic)
28 April 2019	Apple	10
28 April 2019	Orange	50
29 April 2019	Apple	20
29 April 2019	Orange	30

Calendar

Date

28 April 2019

29 April 2019

30 April 2019

Offset

Offset

-365

-364

-363

[...etc...]

-1

REPORT

In my report, users are given the option to filter the calendar table using a date slicer.  They are also given the option to select a comparison date range using an 'Offset (days)' dropdown menu.

This allows me to produce matrix tables like the example below:

Date	Traffic (selected date range)	Traffic (offset / comparison date range)
28 April 2019	60	20
29 April 2019	50	75

MEASURES

Here are the measures I use to acheive this:

--- OffsetValue = SELECTEDVALUE(Offset[Offset],-364)

--- Total Traffic Selcted Date Range = SUM(Performance[Traffic])

--- Total Traffic Comparison Date Range = CALCULATE(SUM(Performance[Traffic]),DATEADD(Calendar[Date],Offset[OffsetValue],DAY))

THE PROBLEM
The method above works great!  However, I have recently managed to obtain the performance data above at an HOURLY level rather than daily.  Because of this, I have to use DateTime data type rather than a standard 'Date'.  

How can I acheive the same date offset methodology outlined above using DateTime rather than Date?

Thanks for your help!

[v-robertq-msft]

Hi, @Anonymous 

According to your description, when you changed the Date type to DateTime type, will the value of Offset[Offset] make corresponding changes?

You can try to change the measure Total Traffic Comparison Date Range like this:

Total Traffic Comparison Date Range =
CALCULATE(
SUM(Performance[Traffic]),
FILTER(
ALL(Performance),
[Date]>=MAX([Date])- Offset[OffsetValue])
&&[Date]<=MAX([Date])))

If this DAX formula can’t help you to get the result you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I'm not sure that will work as I need the comparison data to appear in the same row as the original date range data.

[Anonymous]

Please can someone help with this?  I am deperate for a solution.

[v-robertq-msft]

Hi, @Anonymous 

According to your description, you want to use DateTime data type rather than a standard 'Date' to match your updated dataset, you can try to change the measure [Total Traffic Comparison Date Range] like this:

Total Traffic Comparison Date Range =

CALCULATE(SUM(Performance[Traffic]),DATEADD(Calendar[Date],Offset[OffsetValue],DAY))

To help you in advance, I suggest you to the sample test pbix file(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-robertq-msft ,

Thanks very much for your response.  Yes, this is indeed what I am looking to change.  However, as the DATEADD function only supports data types of 'Date', I'm not sure how this can be acheived.

Do you have any suggestions?  

I will try to provide a sample file in the next few days.

[v-robertq-msft]

Hi, @Anonymous 

According to your description, you said that the DATEADD function does not support data types of 'Date Time'. I think you can try to use the DATEDIFF() function, which can support data types of 'Date Time', like this:

DATEDIFF([start_date], [end_date], Hour)  

More info of DATEDIFF() function

If this result is not what you want, you can post your sample pbix file(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Thanks for your response.

I am still unsure as to how the DATEDIFF function will help to provide a solution to this issue.

Please can you expand on your recommended solution?