cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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. 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

1 ACCEPTED SOLUTION
Solution Sage

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:

Thanks,
Xi Jin.

6 REPLIES 6
Solution Sage

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.

Helper I

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 !

Solution Sage

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.

Helper I

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 :

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

Solution Sage

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:

Thanks,
Xi Jin.

Helper I

Hey @v-xjiin-msft,

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

Nicolas

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors