Week over Week

[TerrificPoet]

Hi,

So I have different query names

I have a count of each query that are being used every week by my users. I'm trying to compare the count from one week to the previous week for the same query. My columns in my matrix are the week numbers. (created a calculated column : Week # = WEEKNUM(TableName1[Search_Date],1)

So for example in week number 34 i would like to have two columns the count for week 34 and the count for week 33. this is why I created COUNT PREVIOUS WEEK = CALCULATE([Measure Count], DATEADD('DATE TABLE'[Date].[Date],-7,DAY)) . I do not understand why it doesnt work as it is a simple dax expression that just calculate the Measure Count from 7 days ago. Also I created a Date Table as you cant use DATEADD with an incomplete date table

Measure Count = SUM(TableName1[Count])

this is how my data looks like

Showing the count for the previous week is not the final step as I would like to add a column that shows the difference between the actual week and the previous one (and color it with conditional formating Red for (-) and green for (+)

If you have any idea to help it would be greatly appreciated 

Thx

[v-xjiin-msft]

Hi @TerrificPoet,

To calculate the previous week data, the date in the expression should be a range. So that we can know that the date is in previous week and a single DATEADD() means nothing. 

Then in my opinion, since you have already defined a week number column. I think it will be easier to get previous week data based on week number. Please try following measure:

COUNT PREVIOUS WEEK =
CALCULATE (
    [Measure Count],
    FILTER (
        ALLSELECTED ( TableName1 ),
        TableName1[Week #]
            = MAX ( TableName1[Week #] ) - 1
    )
)

Thanks,
Xi Jin.

[TerrificPoet]

Hi @v-xjiin-msft,

Thank you very much for you answer I already thought about using the week # as a filter but unfortunately this isnt possible for my dataset as i have data for several years so for example when you have the data for Week 1 of 2017 and you want the data of the previous week doing it this way won't give you the data for week 52 of 2016 is it? it would give you the data for (week 1) - 1 = Week 0 ? But it is better than nothing !

I tried it anyway and it seems to nearly work (except for week 1 but thats normal I can work with week 1 not working) ! my only problem is when i do it your way and I have multiple distinct query in my matrix the Count Previous Week seems to SUM all the queries from last week (See picture)

For example for WEEK 2, i would like in the Previous Count Column the number 664 for google and 660 for Cybergate

I need that in order to create my last column DIFFERENCE BETWEEN WEEK = MEASURE COUNT - COUNT PREVIOUS WEEK

Do you have any idea on how to solve that ?

Thanks again for you help !

[v-xjiin-msft]

Hi @TerrificPoet,

Try this:

COUNT PREVIOUS WEEK =
CALCULATE (
    [Measure Count],
    FILTER (
        ALLSELECTED ( TableName1 ),
        TableName1[Week #]
            = MAX ( TableName1[Week #] ) - 1
    ),
    ALLEXCEPT ( TableName1, TableName1[Query] )
)

If it doesn't work for you, please share some sample data which I can copy and paste directly. So that I can make some proper test.

Thanks,
Xi Jin.

[TerrificPoet]

HI @v-xjiin-msft,

Your second solution gave me the same result as the first one unfortunately.

I created for you a data sample and exported it as an excel file available from this link :

https://drive.google.com/open?id=1aqh6hewx4Oswa0sH2dHSEhnoX7UIJUeT

While i copied the data from Power Bi to Excel I don't know why but the column Search_Date cannot be seen on Excel even thought the data is here ( the column is blank but when you select a cell you can see the data in the function bar)

So to be safe I created for you a second sheet within the Excel file with the same data but i manage to make the data appear ( i copy and pasted the search column data values in an other column and pasted that back to the original column). Use either sheet its basicaly the same data.

In the link you will also find the same data but exported to power Bi and saved as a pbix file.

Thank you very much for your time,

Nicolas

[v-xjiin-msft · Accepted solution]

Hi @TerrificPoet,

Sorry for delay.

Thanks for sharing the sample report. And try this expression: 

Count Previous Week =
CALCULATE (
    [Measure Count],
    FILTER (
        ALLSELECTED ( 'Enterprise_Search' ),
        'Enterprise_Search'[Week #]
            = MAX ( 'Enterprise_Search'[Week #] ) - 1
            && Enterprise_Search[Query] = MAX ( Enterprise_Search[Query] )
    )
)

Also I have modified the expression in your report. Please make a reference:

https://1drv.ms/u/s!AlqSnZZUVHmshWEOzdZEHmTDtULS 

Thanks,
Xi Jin.

[TerrificPoet]

Hey @v-xjiin-msft,

Thank you very much for your help it seems to work perfectly !

Nicolas