Week over Week comparisons in two different graphs.

[TallGuy]

Hi everyone,

Newbie here and I have a question about over time reporting.  I have two tables and want to do a week over week comparison, but in two different graph sets for a total of 4 graphs.  I have omitted the relationship information here because I already have this worked out.

Table_VM_Process (this is a very large table)

Key fields here are:

VM Name

Date

time of Day

Process Name (such as word.exe or sqlserver.exe)

Percent Processor time (or Used CPU by process)

Table_VM_CPU (this is a separate datasource with much fewer records.)

Key fields here are:

VM Name

Date

time of day

Total Used CPU

My "plan" is to have at least 3 slicers

1. VM Name

2. Date

3. Process Name

The main goal is to see if a specific process is happening during the same times between the weeks.

I have this built now using single date with the Table_Process and Table_CPU information as a graph....and it works great.  But I'm being asked "Well can you show me a side by side comparison to show the previous weeks information as well?".  The result might look something like this, and is where my question is.

I do have a date table to get the previous week, but I don't know/understand/haven't learned how to assign the correct date values for the previous weeks graphs. I would imagine it to be something like slicerDateValue - 7, but i'm a bit lost on how to accomplish this.

Suggestions or links to other articles are gleefully welcome.

Thanks in advance.

[Anonymous]

Hi @TallGuy ,

Please try this measure:

NewMeasure = CALCULATE([YourMeasure],DATEADD('Date'[Date],-7,DAY))

It works by moving the time horizon of the measure forward by 7 days.

DATEADD - DAX Guide

Also see this blog post on the measure [Sales PW]

Week-related calculations – DAX Patterns

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[hnguy71]

hey @TallGuy ,

Seems like an easy and straightforward request. I am assuming you're counting the number of interactions for each of your tables. so a basic measure using the DATE function like this would work:

Previous Week = 
-- Find date in context, if none is selected, default to last date in source
VAR _SelectedDate = SELECTEDVALUE('DATE'[Date], MAX('DATE'[Date]) )
-- what's your threshold? could set this dynamically, but 7 days as a sample is okay
VAR _Threshold = 7
-- turn back time by 7 days
VAR _StartDate = DATE( YEAR(_SelectedDate), MONTH(_SelectedDate), DAY(_SelectedDate) - _Threshold)
VAR _EndDate = _SelectedDate - 1

RETURN

-- output
CALCULATE(COUNTROWS(Table_VM_Process), DATESBETWEEN('DATE'[Date], _StartDate, _EndDate), ALL('DATE'[Date]))