Forum Discussion

marsclone's avatar
marsclone
Helper IV
7 years ago
Solved

Measure last dataset

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 ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

marsclone's avatar
marsclone
Helper IV
7 years ago
Hi @v-juanli-msft,
 
First my answers, for a testfile i have to manipulate the dataset, that takes time.
 
1) All files are Excel files
2) Each file shows the outstanding items of our debtors on a specific date, this specific date is shown in the file
3) Today i upload a list per 2019.6.4 then Power Bi only shows (automatic) the values of this date
4) But also show the difference from last week 2019.5.28
 
I will try to make a testfile.
 
Thank you!
Regards Marcel
v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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.

  • marsclone's avatar
    marsclone
    Helper IV
    7 years ago

    Hi @v-juanli-msft,

     

    Thank you very much. 

    This worked!

     

    Regards

    Marcel