Forum Discussion

TallGuy's avatar
TallGuy
New Member
1 year ago

Week over Week comparisons in two different graphs.

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.
Need Help

2 Replies

  • hnguy71's avatar
    hnguy71
    Super User
    1 year ago

    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]))

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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