Weekly values and their variations

[Anonymous]

Hi Power Bi community, need you help again 

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

[v-chuncz-msft]

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

[Anonymous]

Hi @v-chuncz-msft

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]

@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:

http://community.powerbi.com/t5/Desktop/How-can-I-calculate-absolute-difference-between-two-consecutive/m-p/220413#M97842

It's pretty difficult to work around, when you want to slice on categories unless you use the date. 

[Anonymous]

Hi @Anonymous and @v-chuncz-msft

the idea is to arrive to something similar to this:

In this model that I've developed months ago, variations work through a back end excel model that with 1/0 cells activates or de acitvates updates and automatically calculates the difference.

This new versions have to have everything implemented within power bi to enable more comparison, even between week 1 and week -12 if it comes to.

Coming to your point, yes categories have to be sliced.

'when you want to slice on categories unless you use the data', what do you mean? 

[Anonymous]

Whoops, it was a type. here is the corrected version:

'when you want to slice on categories unless you use the date'

This is because we can add conditions to the date like: -7 days or w.e. i might be. 

[Anonymous]

@Anonymous aha no worries.

Yeah, as  I told you yesterday the -7 (differential day-based) could work but what happens if we do not update the data for 1 week?

this kind of data is genrally refreshed every monday morning but it could happen that we skip 1 monday.

Therefore week number would be a better index as it consider, for the sake of conversation:

let's say today: week 20

last refresh: week 19

so the difference between this week and last week should be a good driver as it can overcome the issue of missing data.

what do you think?

[Anonymous]

@Anonymous

I completely understand your position. If I were you I would try to make sure (somehow) that the data is always updated/refreshed with the right interval. So if you refresh one day too late, I'd make sure the data is still according to the appropriate intervals if that makes sense. The reason I'm trying to make you do that is because I haven't seen a solution like the one you are asking for before. Check out the link in my last post for more information about that.

[Anonymous]

@Anonymous

but even if I go down to your suggestion of the -7 days, your yesterday's solution was inapproriate as it considered the sum of the levels instead the single weekly level.

I looked to the post that you referenced and it was unsolved too. I think that either ways (week number or day difference) are fine but there are other issues to be addressed too.

[Anonymous]

@Anonymous

When I tried it on my local computer, it worked out between different levels, so I can't repoduce that.

The post was just to demonstrate that we haven't had success with creating a solution with a "dynamic index".

[Anonymous]

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

[Anonymous]

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]

 @Anonymous

I'm not aware of how to go about that. I've tried myself to make the same method work, but creating such a criteria in an aggregation isn't very straight forward when using dax.

[Anonymous]

@Anonymous

thank you anyway 🙂 appreciated. 

althought I believe the formula is mispelled. i've tried but does not go through

[Anonymous]

@Anonymous

What DAX formula did you try out?

[Anonymous]

@Anonymous: the first one... 

also i've replaced ; with , (i think in a UK computer i need ,)