Skip to main content
cancel
Showing results for
Search instead 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 III

## Looking for Tips on Storing Data

Hello,

I am an Digital Marketing Professional and recently started using Power BI for representing data to the stackholders. Currently I am working with a simple data which I need to present with lots of complicated calculations. Here are the headings of data I am working with:

 partnumber mktname totquantity Week Year

Week and Year is for Slicer; main issue is with other fields.

“totquantity” field contains data of how much units have been sold and all the data is flagged with week and year. Now let me explain what my requirement is:

Suppose I select Year “2018” and Week “42” in slicer; “totquantity” will show week 42 data naturally – along with that I want 3 other columns will be there where I can see “totquantity” of Week 41, Week 40 and Week 39 respectively.

First of all I don’t whether I able to explain the situation properly or not; if not please let me know – I will try to explain it as much as possible.

Secondly, I don’t whether the requirement is technically possible or not.

I am open with all type of reply.

Regards

Utsav

1 ACCEPTED SOLUTION
Community Support

Create measures in your table

```measure =
CALCULATE (
SUM ( Sheet2[Totquantity] ),
FILTER (
ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
[Week]
= SELECTEDVALUE ( Sheet2[Week] )
&& [Year] = SELECTEDVALUE ( Sheet2[Year] )
)
)

measure1 =
CALCULATE (
SUM ( Sheet2[Totquantity] ),
FILTER (
ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
[Week]
= SELECTEDVALUE ( Sheet2[Week] ) - 1
&& [Year] = SELECTEDVALUE ( Sheet2[Year] )
)
)

measure2 =
CALCULATE (
SUM ( Sheet2[Totquantity] ),
FILTER (
ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
[Week]
= SELECTEDVALUE ( Sheet2[Week] ) - 2
&& [Year] = SELECTEDVALUE ( Sheet2[Year] )
)
)

measure3 =
CALCULATE (
SUM ( Sheet2[Totquantity] ),
FILTER (
ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
[Week]
= SELECTEDVALUE ( Sheet2[Week] ) - 3
&& [Year] = SELECTEDVALUE ( Sheet2[Year] )
)
)```
```current week = IF([measure]<>BLANK(),[measure],0)

current week-1 = IF([measure1]<>0,[measure1],0)

current week-2 = IF([measure2]<>BLANK(),[measure2],0)

current week-3 = IF([measure3]<>BLANK(),[measure3],0)```

Best Regards

Maggie

8 REPLIES 8
Community Champion

@utsavlexmark If you can post some sample data and expected output that will be helpful to understand better.

Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion

Helper III

Hello pattemmanohar,

Thanks for your reply. Here I am trying to provide the info you need. Please let me know if you have more doubts, I would love to explain.

Database

 Partnumber Mktname Totquantity Week Year A0001 Product A 1 42 2018 A0001 Product A 2 40 2018 A0002 Product B 2 42 2018 A0002 Product B 1 39 2018 A0003 Product C 1 42 2018 A0003 Product C 2 41 2018 A0004 Product D 2 42 2018 A0004 Product D 1 40 2018

Output

Slicer 1=Week=42(selected)       Slicer 2=Year=2018(selected)

 Partnumber Mktname Totquantity(Week39) Totquantity(Week40) Totquantity(Week41) Totquantity(Week42) A0001 Product A 0 2 0 1 A0002 Product B 1 0 0 2 A0003 Product C 0 0 2 1 A0004 Product D 0 1 0 2
Community Support

Create measures in your table

```measure =
CALCULATE (
SUM ( Sheet2[Totquantity] ),
FILTER (
ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
[Week]
= SELECTEDVALUE ( Sheet2[Week] )
&& [Year] = SELECTEDVALUE ( Sheet2[Year] )
)
)

measure1 =
CALCULATE (
SUM ( Sheet2[Totquantity] ),
FILTER (
ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
[Week]
= SELECTEDVALUE ( Sheet2[Week] ) - 1
&& [Year] = SELECTEDVALUE ( Sheet2[Year] )
)
)

measure2 =
CALCULATE (
SUM ( Sheet2[Totquantity] ),
FILTER (
ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
[Week]
= SELECTEDVALUE ( Sheet2[Week] ) - 2
&& [Year] = SELECTEDVALUE ( Sheet2[Year] )
)
)

measure3 =
CALCULATE (
SUM ( Sheet2[Totquantity] ),
FILTER (
ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
[Week]
= SELECTEDVALUE ( Sheet2[Week] ) - 3
&& [Year] = SELECTEDVALUE ( Sheet2[Year] )
)
)```
```current week = IF([measure]<>BLANK(),[measure],0)

current week-1 = IF([measure1]<>0,[measure1],0)

current week-2 = IF([measure2]<>BLANK(),[measure2],0)

current week-3 = IF([measure3]<>BLANK(),[measure3],0)```

Best Regards

Maggie

Helper III

@v-juanli-msft, after a long time I need to resume this post again. Initially I thought this process is working perfectly - but now I found some discripencies are there in the process.

Let me try to explain the situation; currently I have included data till Week52(current week). When I am creating the table Week 52, Week 51, Week 50 and Week 49 is showing. I have created one column for avarage of last 4 weeks. We have one process to validate this data and forund in the table data are matching. But the Avearge of Last  4 Weeks is not matching.

When I downloaded the Dashboard data, I found Downloaded data of Week 50 is not matching with what I saw in table and that is the reason the last 4 Weeks Average is not matching.

Detail analysis is showing that this problem is occuring randomly for different weeks.

I guess you defenitely have a solution for this issue.

Hope I am clear with the explation - if not please let me know.

Regards

utsavlexmark(Utsav)

Helper III

Hello Maggie,

Just wow; I could not explain - how happy I am. I was knocking my head in wall to do this for last one month.

Thank you very much.

Not only, this trick will help many more in future.

Thanks from all of them in Advance.

Regards

Utsav

Helper III

Thanks Maggie,

Let me try all these tricks - I hope will be able to implement as directed by you, I fI found any issue - will let you know.

Regards

Utsav

Community Champion

@utsavlexmark That means, if you select week 40 then you want to display week 39 and week 40 data only isn't it ?

Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion

Helper III

No, what I want is - when I will select a week in Slicer - in the table data of that week and preceeding 3 weeks will be shown. This is actually to see how a particular product is performing in last 4 weeks including the selected week.

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors