*UPDATED* still need help creating the time intelligence visual I have in mind

[ERingger]

Hi there,

I am working with a customer complaint database for a municipal water provider. I have a direct link to an SQL server/database which automatically populates my dashboard visuals with new data. This data table is over 100,000 rows and each row indicates a specific customer call. 

I am hoping to create a dashboard that helps contextualize the current week's call data compared to call data in the past. Specifically, I would like to create a visual that shows the number of calls in the last week compared to how many calls we typically receive for the same date range in previous years. For example: I would like to know how many calls (rows) there were in the last week, so 4/3/24 through 4/10/24. I would like to compare this number to the number of calls we recieved between 4/3/24 and 4/10/24 over the last 5 years. So an average of the amount of calls in that week from 2019-2023, compared to the amount we recieved this week in 2024. 

I have created an example pbix file encorporating advice I got from @audreygerred , but it is still not working. Using advice from multiple forums I have gotten to this point: 

The total calls measure for 4/4/24-4/10/24 is correct at 12, and my average measure is correctly averaging the other 5 years, but all the other measures are producing the wrong numbers and I have no idea how those numbers were generated. If those measures were achieving what I had hoped, Calls LY, LYY, LYYY, LYYYY, and LYYYYY would all be 7. Then my average should also be showing 7. 

Could someone please go into my example pbix file and take a look at my tables/DAX measures and see what I am doing wrong, and tell me what to change in order to produce my desired result? Many thanks. 

[v-tangjie-msft · Accepted solution]

Hi @ERingger ,

Thanks @audreygerred  for the quick reply. I have some other ideas for you to consider:
I don't have access to open your pbix file, I created some test data.

We can create measures.

this_week = CALCULATE(SUM('Table'[Number of calls]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=TODAY() && 'Table'[Date]>=TODAY()-7))

LY = 
var _end_date=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
var _start_date=_end_date-7
return CALCULATE(SUM('Table'[Number of calls]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=_end_date && 'Table'[Date]>=_start_date))

LLY = 
var _end_date=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY()))
var _start_date=_end_date-7
return CALCULATE(SUM('Table'[Number of calls]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=_end_date && 'Table'[Date]>=_start_date))

LLLY = 
var _end_date=DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY()))
var _start_date=_end_date-7
return CALCULATE(SUM('Table'[Number of calls]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=_end_date && 'Table'[Date]>=_start_date))

LLLLY = 
var _end_date=DATE(YEAR(TODAY())-4,MONTH(TODAY()),DAY(TODAY()))
var _start_date=_end_date-7
return CALCULATE(SUM('Table'[Number of calls]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=_end_date && 'Table'[Date]>=_start_date))

LLLLLY = 
var _end_date=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY()))
var _start_date=_end_date-7
return CALCULATE(SUM('Table'[Number of calls]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=_end_date && 'Table'[Date]>=_start_date))

Avg = DIVIDE([LY]+[LLY]+[LLLY]+[LLLLY]+[LLLLLY],5)

Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[audreygerred]

Hello! First, you would just create your measure for the total calls, then you would create one for LY (assuming you have a date table marked as a date table), you would do the measure like this:

Calls LY = CALCULATE([Calls], SAMEPERIODLASTYEAR('Date Table'[Date]))

If you want to compare the calls from this week to two years ago you would do

Calls LLY = CALCULATE([Calls], DATEADD('Date Table'[Date],-2,year))  <-- you could also use DATEADD for the LY measure

For comparing TY to LLLY use -3, for LLLLY use -4, for LLLLLY use -5.

Now, as long as your date table has week number you would be good to go (ideally instead of comparing the same dates YoY you would just use the same week number). If you had a matrix you could put the week numbers in rows, then put your [Calls] and the other 5 measures in values. Filter to 2024 and viola! You will have call volumes per week for TY and how those numbers compare to each week for the 5 previous years. If you want to do it at the day, month, year, qtr level just change the fields in the visual.

[ERingger]

@audreygerred Thank you so much! I will try this out. Is there any way you can compare the current week to an average of the previous 5 years rather than comparing against each past year individually? Also, if I plug in exact dates, will it automatically update? Like if I log in tomorrow rather than today, is there a way to get it to be 4/4/24 through 4/11/24 rather than remaining stagnant at 4/3/24 - 4/10/24? 

[audreygerred]

You would just have to create a meausure for the average of the last 5 years then a measure to compare your this year to that average. 

If you have your report filter to be set to a relative date (i.e. is in this week) or something like is in the last 7 days it will know what to do based on the relative date.

[ERingger]

@audreygerred okay, thanks so much! would you be able to write out how you might go about creating those measures in DAX?