Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Previous week data based on selected week from slicer

Hi guys,

 

I'm just learning the magic of PowerBi by building a weekly report.

However, I got stuck and I really hope you guys can point me in the right direction.

 

Simply put, my table has a column with created apps, and I want to visalize the distinct apps within a chosen period of time.

Annotation.pngtables.png

 

I have a measure for this:

Distinct Apps =
CALCULATE(DISTINCTCOUNT(AllData[AppId]))
 
I have WeekNumber column also, which is put in a slicer
WeekNumber = WEEKNUM(AllData[CreatedDate],2)
 
I created 4 new measures to get me the previous week data, but I can't figure it out why the last one does not work(it shows blank):
Selected Week = SELECTEDVALUE(AllData[WeekNumber])
 
Selected Week Distinct Apps =
var selectedWeek = SELECTEDVALUE(AllData[WeekNumber])
return CALCULATE([Distinct Apps], FILTER(AllData, AllData[WeekNumber]=selectedWeek))
 
Previous Week to Selected Week = SELECTEDVALUE(AllData[WeekNumber])-1
 
Last Week Distinct Apps =
calculate([Distinct Apps], FILTER((AllData), AllData[WeekNumber]=[Previous Week to Selected Week]))
example.png
This last measure should show the value 6, which corresponds for week 26, but it keeps showing blank, making me pull my hair off..
all data.png
Can you please have a look and demistify what's going on and why I cannot display the previous week data, and how can i do that?
 
Thank you for your time!
Daniela

 

 

 


 
 
 
 
 
 
 
 
1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

Hi @Anonymous ,

It is caused that the value is filtered by the slicer (Week = 27). You could try the following formula with the function of ALL. And you need to redefine the selected value in the measure.Because the measure of Previous Week to Selected Week is still affected by the slicer.

Last Week Distinct Apps = 
var a = SELECTEDVALUE(AllData[WeekNumber])
return
CALCULATE(DISTINCTCOUNT(AllData[AppId]), FILTER(ALL(AllData), AllData[WeekNumber] = a-1))

3.PNG

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xuding-msft
Community Support
Community Support

Hi @Anonymous ,

It is caused that the value is filtered by the slicer (Week = 27). You could try the following formula with the function of ALL. And you need to redefine the selected value in the measure.Because the measure of Previous Week to Selected Week is still affected by the slicer.

Last Week Distinct Apps = 
var a = SELECTEDVALUE(AllData[WeekNumber])
return
CALCULATE(DISTINCTCOUNT(AllData[AppId]), FILTER(ALL(AllData), AllData[WeekNumber] = a-1))

3.PNG

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello @v-xuding-msft and thank you millions for your interest and reply!

 

You are magnificent, you helped me a lot!

I was able to solve my issue by following your formulas and adding yet another filter.

What I forgot to mention was that I had a global filter by the [Origin] of the app.

Therefore, using ALL function ignored this filter and returned the total unique values of last week.

 

This [Origin] column was in another table, so I related my AllData table with that column:

AllData[Origin] = RELATED(Application[Origin])

 

I figured I can add a second filter to the calculate function to keep that filter running.

And TA-DAAA, it gave me the previous week unique values filtered by origin :), like below:

Last Week Distinct Apps = 
var a = SELECTEDVALUE(AllData[WeekNumber])
return
CALCULATE(DISTINCTCOUNT(AllData[AppId]), FILTER(ALL(AllData), AllData[WeekNumber] = a-1), FILTER(ALL(AllData), AllData[Origin] = "origin1")

 

Without your explanation, I would have been stuck forever!

So thank you so so so much!

 

Have great day,

Daniela

How to handle end of year where week 52 becomes week 1

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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