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
ElinaK
Regular Visitor

Filter last 30 days from existing data and add filter for top respondents for two columns

Hi,

 

I have 3 questions:

1. I'm creating a dasboard where I should show 30 days rolling number in a timeline graph + total number separately.

How to filter last 30 days from the dashboard so that what ever filter you choose you always get the data from the maximum date available backwards 29 days? This is simple question I think but I wasn't able to do this. So I don't want to use today() but maximum date.

2. Is there a way to do rolling 30 days for a timeline so that it would also show the previous 30 days period backwards and  also previous ones. I mean if there is in a data the latest day 3.1.2017 you will see always that day cumulative total from backwards -29 days. Then you should see  in a timeline previous 30 days cumulative on 4th Dec which calculates the data from backwards again -29 days and so on. Is this possible to do in Power BI?

3. I have survey data and I need to filter my kpi so that when there are 100 respondents for a country and certain product level it will be shown , otherwise no kpi is shown. How to do this in Power BI?

 

I appreciate if someone can help me with these ones! Number 1 and 3 are most important ones.

 

Br, Elina

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @ElinaK,

 

For your rquirement, you can refer to below methods.

 

Q1. You can use lastdate function to filter the specify date range.

 

FilteredTable = FILTER(ALL('Table'),[Date]>=LASTDATE(VALUES('Table'[Date]))-30 &&[Date]<=LASTDATE(VALUES('Table'[Date])))

 

Q2. You can write a measure which used to calculate the last 30 days rolling total, then create a visual and drag date to axis. and put the measure to value fields.

 

SUMX(Filter(All(FilteredTable), [Date]<=Max([Date])),[Amount])

 

Q3. Try use visual level filter to filter your data, modify the filter type to topN.

 

 Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi,

 

Thank you!  I need to anyhow filter the data in a dashboard, so I cannot use ALL function and did not get this work yet.

So if I filter the specific product and specific country I should have the latest maximum date for that comparison and then -29 days backwards.

And in a timeline they want to see cumulative result for the latest day and then backwards -30 days you see the previous cumulative result from last 30 days like in a picture attached.  and based on the selections the total results for selected product and country must be at least 100 for whole time period before the figure is even shown.30 days running sum.png

Hi @ElinaK,

 

You can try to use below formula if it suitable for your requirement:

 

Running total=
var currCountry= LastNoBlank(Table[Country],[Country])
var lastDateOfCountry= MAXX(FILTER(ALL(Table),[Country]=currCountry),[Date])
return
If(AND(Max(Table[Date])>=lastDateOfCountry-30,Max(Table[Date])<=lastDateOfCountry), 
SUMX(Filter(All(Table),[Country]=currCountry && [Date]<=lastDateOfCountry),[Amount]),
Blank())

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.