Matrix with DoD (trending data) w/ 7 Day and 14 Day volume change

[bolwahnn]

Hey Guys,

I cant figure out how to create a visual that shows day over day data with a 7 and 14 day volume change on the most recent date. I already created a measure to calculate the 7 and 14 Day difference, but when I put it into the matrix, it does the calc for each date.....

Here's what I want (example)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]4/1[/TD]
[TD]4/2[/TD]
[TD]4/3[/TD]
[TD]4/4[/TD]
[TD]4/5[/TD]
[TD]4/6[/TD]
[TD]4/7[/TD]
[TD]Volume Diff (6 Day)[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]+7[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]13[/TD]
[TD]10[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD]Client C[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Client D[/TD]
[TD]20[/TD]
[TD]15[/TD]
[TD]13[/TD]
[TD]2[/TD]
[TD]15[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]+20[/TD]
[/TR]
</tbody>[/TABLE]

Here's what I"m getting (example)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]4/1[/TD]
[TD]4/1[/TD]
[TD]4/2[/TD]
[TD]4/2[/TD]
[TD]4/3[/TD]
[TD]4/3[/TD]
[TD]4/4[/TD]
[TD]4/4[/TD]
[TD]4/5[/TD]
[TD]4/5[/TD]
[TD]4/6[/TD]
[TD]4/6[/TD]
[TD]4/7[/TD]
[TD]Volume Diff (6 Day)[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]8[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]9[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]12[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]15[/TD]
[TD]+7[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]15[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]12[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]11[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]8[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]13[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD]Client C[/TD]
[TD]10[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Client D[/TD]
[TD]20[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]15[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]13[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]2[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]15[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]30[/TD]
[TD]Volume Diff (6 Day)[/TD]
[TD]40[/TD]
[TD]+20

[/TD]
[/TR]
</tbody>[/TABLE]

* Where it says Volume Diff (6 day) my measure shows up after eachday doing the calc 6 days back ---- But I just want it on the last date..

Hope this makes sense.....Appreciate any help.

Thanks

 

[Matt Allington]

Well, I guess your measure is wrong. How about you post your data model relationship view and your measure.

 

[bolwahnn]

Well, I guess your measure is wrong. How about you post your data model relationship view and your measure.

I may not be explaining correctly.... The measure works in that it gives me the volume difference between dates, but its doing the calculation for every date. I only want the last date to provide the difference in volume. So I have Day over Day trending data and at the end, a 7 day variance from 4/1 to 4/8.

Like this
[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl68, width: 64"]4/1/[/TD]
[TD="class: xl68, width: 64"]4/2[/TD]
[TD="class: xl68, width: 64"]4/3[/TD]
[TD="class: xl68, width: 64"]4/4[/TD]
[TD="class: xl68, width: 64"]4/5[/TD]
[TD="class: xl68, width: 64"]4/6[/TD]
[TD="class: xl68, width: 64"]4/7[/TD]
[TD="class: xl68, width: 64"]4/8[/TD]
[TD="class: xl67, width: 64"]7 Day diff[/TD]
[/TR]
[TR]
[TD="class: xl67"]10[/TD]
[TD="class: xl67"]12[/TD]
[TD="class: xl67"]13[/TD]
[TD="class: xl67"]14[/TD]
[TD="class: xl67"]15[/TD]
[TD="class: xl67"]16[/TD]
[TD="class: xl67"]17[/TD]
[TD="class: xl67"]18[/TD]
[TD="class: xl67"]+8[/TD]
[/TR]
</tbody>[/TABLE]

Instead, I'm getting a 7 Day diff after every date,i.e. so 4/7 is doing the 7 day diff calc from 3/31, 4/6 is doing the calc from 3/30, and so on and so forth.

I just want the 7 day diff calc on the last reported date - Not on every date.

 

[Matt Allington]

Are you using Excel? If so
1. Create your pivot that shows the values you don't want.
2. click in the pivot
3. go to Analyze/Fields, Items and Sets
4. Create new set based on column items
5. Delete the individual items you don't want to see

 

[bolwahnn]

Are you using Excel? If so
1. Create your pivot that shows the values you don't want.
2. click in the pivot
3. go to Analyze/Fields, Items and Sets
4. Create new set based on column items
5. Delete the individual items you don't want to see

Not in Excel.... I'm trying to build this in PowerBI, not sure if its possible---- I was utilizing the Matrix visual, but it does the calc/measure on each date when I only need it at the end (on the last date published).

 

[Matt Allington]

This is an asymmetrical matrix which is harder to do. You could probably do it using a disconnected table to pass the measures, similar to this.
https://exceleratorbi.com.au/measures-on-rows-here-is-how-i-did-it/