Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
Regards,
Xiaoxin Sheng
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.
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |