cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
muird03
Frequent Visitor

Performance vs Comparison Date Range using DateTime

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

DateProduct NameTraffic (web traffic)
28 April 2019Apple10
28 April 2019Orange50
29 April 2019Apple 20
29 April 2019Orange 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.
 
Capture.PNG
This allows me to produce matrix tables like the example below:
DateTraffic (selected date range)Traffic (offset / comparison date range)
28 April 20196020
29 April 201950

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!

7 REPLIES 7
v-robertq-msft
Community Support
Community Support

Hi, @muird03 

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.

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.

muird03
Frequent Visitor

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

v-robertq-msft
Community Support
Community Support

Hi, @muird03 

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.

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.

Hi, @muird03 

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.

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors