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 week?

Another measure i assume?

 

Which measures do i need to achieve this?

 

Thank you in advance

Regards Marcel

 

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.

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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

     

    • 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.