Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Every week, i put a new dataset in a folder.
I want Power Bi to use (automatic) this latest data to show my values.
Can i achieve this with a measure?
I also want to show the difference with last week?
Another measure i assume?
Which measures do i need to achieve this?
Thank you in advance
Regards Marcel
Solved! Go to Solution.
Hi @marsclone
1. Open Edit Queries,
New source->select Folder->select your folder,
Click on the button on "content"
select any files from the pop-out window,
then get a table with data from all files from this folder.
2.Add two custom column
today=DateTime.Date(DateTime.LocalNow())
day difference=Duration.Days([today]-[date])
3. Filter "day difference"
4. Close&&apply, go to report view
create a calendar table
calendar = CALENDARAUTO()
create relationship
5. Create measures in "weekly data" table
today = TODAY() flag = IF([today]=MAX('calendar'[Date]),1,0) last week data = CALCULATE(SUM('weekly data'[value]),FILTER(ALL('calendar'),'calendar'[Date]=MAX('calendar'[Date])-7))
6. Add [date] from "calendar" table in the table visual,
add [flag] in the viusal level filter of the table visual
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @marsclone
It should be possible with Power BI Desktop.
Could you tell me more details so that i can test and provide solid solutions?
or could you show an example with data or screenshot?
questions:
1. which files do you add in the folder every week? excel?
2. do you have data in every single (excel) file or have data all in one (excel) file?
3. how do you define last week?
let's to say, today is 2019.6.4, i add last whole week's data(2019.5.27-6.2) in the folder,
refresh from power bi desktop/service, it shows current value from 2019.5.27-6.2,
then the last week's data should be from 2019.5.20-5.26,
Is my understanding correct?
Best Regards
Maggie
Hi @marsclone
1. Open Edit Queries,
New source->select Folder->select your folder,
Click on the button on "content"
select any files from the pop-out window,
then get a table with data from all files from this folder.
2.Add two custom column
today=DateTime.Date(DateTime.LocalNow())
day difference=Duration.Days([today]-[date])
3. Filter "day difference"
4. Close&&apply, go to report view
create a calendar table
calendar = CALENDARAUTO()
create relationship
5. Create measures in "weekly data" table
today = TODAY() flag = IF([today]=MAX('calendar'[Date]),1,0) last week data = CALCULATE(SUM('weekly data'[value]),FILTER(ALL('calendar'),'calendar'[Date]=MAX('calendar'[Date])-7))
6. Add [date] from "calendar" table in the table visual,
add [flag] in the viusal level filter of the table visual
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-juanli-msft,
Thank you very much.
This worked!
Regards
Marcel
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |