Forum Discussion

TerrificPoet's avatar
TerrificPoet
Advocate I
8 years ago
Solved

Week over Week

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...
Need Help
Tutorial Requests
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

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's avatar
TerrificPoet
Advocate I
8 years ago

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's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    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's avatar
      TerrificPoet
      Advocate I
      8 years ago

      Hey v-xjiin-msft,

       

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

       

      Nicolas