Forum Discussion
Weekly values and their variations
Hi Power Bi community, need you help again :smileyvery-happy:
The next step for our amazing benchmark tool will be comparing levels across weeks: the aim is spotting the increases or decreases of fares between the this week and last week. Therefore periods are expanded as the snapshot suggests:
Let's say one measure should return week 21 level, another one week 20 level and another one highlight the difference between the two (ok I don't need help for this).
looking forward to have your views.
cheers
15 Replies
Hi Anonymous
I'm here to help you!
Try this measure out:
Sum of Value this week = CALCULATE(SUM(TABLE[Value]);DATESINPERIOD(TABLE[Date];LASTDATE(TABLE[Date];-7;DAY)
Sum of Value last week = CALCULATE([Sum of Value this week];DATEADD(TABLE[Date];-7;DAY)
Sum of Value this week - Sum of Value last week = [Sum of Value this week] - [Sum of Value last week]
Let me know if this works out. If you need further help, could you possibly provide some test data, whichs simulates your data?
Best,
Martin
Hi Anonymous,
thank you for your feedback. The only thing is that AIF values do not have to be summed up: the aim of this differential is to quantify the AIF variation between the last and the prev week.
Also, data feed is generally updated weekly so the -7 day rule should be ok but sometimes can happen that the update falls in different period. That's why i've included the week number in the dataset so you can compare the last and the last -1.
let me know if it's clear
cheers
albe
Anonymous,
Simply add calculated columns as shown below.
Prev = LOOKUPVALUE ( Table1[AIF], Table1[Week number], Table1[Week number] - 1 )
Variation = IF ( ISBLANK ( Table1[Prev] ), BLANK (), Table1[AIF] - Table1[Prev] )
thank you for your idea. I've tried this way but what i got once i make the first column:
PREV = LOOKUPVALUE(VARIATIONS[AIF],VARIATIONS[WEEK NUMBER],VARIATIONS[WEEK NUMBER]-1)
A table of multiple values was supplied where a single value was expected.
Would not be better to use measure for this?
Take into consideration that this difference needs to be applied to different CXR (QF, QR, SQ, etc) that in my screenshot are not showing but the idea is highlighting the difference between 2 periods of every CXR.
let me know if you have any other idea
Anonymous
So you want to calculate variations in your data from week to week and you also want to slice on some category within your data?
There were a similar thread some time last week:
It's pretty difficult to work around, when you want to slice on categories unless you use the date.
