Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
dpowerbiuser
Frequent Visitor

Want to see the list of modified records every day in powerbi report

Hi Team,

I have one requirement .  I am taking snapshot of daily data into the below table.

 

dpowerbiuser_2-1697072317558.png

 

My requirement is to find the modified records comparing previous days "task date" and "total tasks" columns . If any new records got added then those records also to be added in output  Example : if we compare 10/4/2023 and 10/5/2023 modified_date , new records got added .

 

if we compare 10/5/2023 and 10/6/2023 modified_date , 4 records got modified  and one record got added .

 

Expected output would be

Locationgroup namePrevious task datetask dateprevious total taskstotal tasksModified_date
Location5groupC 18-11-23 310/5/2023
Location1groupA21-10-2324-11-2331010/6/2023
Location2groupA28-10-2301-12-236710/6/2023
Location3groupB04-11-2322-11-2310110/6/2023
Location4groupC18-11-2301-11-239110/6/2023
Location6groupC 18-11-23 210/6/2023

 

 

Kindly help

7 REPLIES 7
v-zhangti
Community Support
Community Support

Hi, @dpowerbiuser 

 

You can try the following methods.
Measure:

Previous total tasks = 
Var _PrevMdate=MINX(FILTER(ALL('Table'),[Modified_date]<SELECTEDVALUE('Table'[Modified_date])&&[Location]=SELECTEDVALUE('Table'[Location])&&[group name]=SELECTEDVALUE('Table'[group name])),[Modified_date])
Var _Prevtotaltasks=CALCULATE(MAX('Table'[total tasks]),FILTER(ALL('Table'),[Modified_date]=_PrevMdate&&[Location]=SELECTEDVALUE('Table'[Location])&&[group name]=SELECTEDVALUE('Table'[group name])))
Return
_Prevtotaltasks
Previous task date = 
Var _PrevMdate=MINX(FILTER(ALL('Table'),[Modified_date]<SELECTEDVALUE('Table'[Modified_date])&&[Location]=SELECTEDVALUE('Table'[Location])&&[group name]=SELECTEDVALUE('Table'[group name])),[Modified_date])
Var _Prevtaskdate=CALCULATE(MAX('Table'[task date]),FILTER(ALL('Table'),[Location]=SELECTEDVALUE('Table'[Location])&&[group name]=SELECTEDVALUE('Table'[group name])&&[Modified_date]=_PrevMdate))
Return
_Prevtaskdate
Measure = 
Var _Prevtotaltasks= [Previous total tasks]
Var _MinMdate=CALCULATE(Min('Table'[Modified_date]),ALL('Table'))
Return
IF(SELECTEDVALUE('Table'[Modified_date])=_MinMdate,0,IF(SELECTEDVALUE('Table'[total tasks])=_Prevtotaltasks,0,1))

vzhangti_0-1697534662270.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thank you so much . If  I have more number of days data(example: Modified_Date 4th October, 5th Oct,6th oct ,7th Oct etc  ) then it is not working as expected  .It is always show previous task date as min(task date)ie here  4th Oct . 

123abc
Community Champion
Community Champion

  1. Load your data into Power BI:

    • Open Power BI Desktop.
    • Go to "Home" and click on "Get Data."
    • Choose your data source (e.g., Excel, SQL Server, etc.) and import your data into Power BI.
  2. Create a new calculated column for "Modified_date":

    • In Power BI Desktop, select the table where your data is located.
    • Click on the "Model" view.
    • Click on "New Column" in the "Modeling" tab.
    • Use a DAX formula to create the "Modified_date" column, which compares the "task date" with the previous day's "task date." Here's an example formula:

Modified_date = IF(
NOT ISBLANK([task date]) && NOT ISBLANK([Previous task date]),
IF([task date] > [Previous task date], [task date], [Previous task date])
)

 

Create a new calculated column for "Modified Indicator":

  • Use another DAX formula to determine if a record is modified. You can use the "total tasks" column for this. For example:

Modified Indicator = IF([total tasks] <> [previous total tasks], "Modified", "Unmodified")

 

  1. Create a table visual with the desired columns:

    • In the "Fields" pane, select the table where you added the calculated columns.
    • Add the following columns to your table visual:
      • Location
      • Group name
      • Previous task date
      • Task date
      • Previous total tasks
      • Total tasks
      • Modified_date
      • Modified Indicator
  2. Apply filters:

    • To see the list of modified records for a specific day, you can use the "Modified_date" column as a filter. Add a slicer or a filter visualization that allows you to select a specific date.

With these calculated columns and the filter, you'll be able to see the modified records for a particular day in your Power BI report. The "Modified Indicator" column helps you quickly identify which records have been modified.

 
 
 
 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Apologies for the confusion .Actually first table is my input table which doesnot have "previous total task" column. 2nd table is the expected output  which contain "previous total task" column

I understand your clarification. To generate the "previous total tasks" column in your output, you can use a similar approach to calculate it using DAX. Here's how you can modify the DAX formula to calculate the "previous total tasks":

 

Previous total tasks =
VAR PreviousDate = [task date] - 1
VAR PreviousTotalTasks = CALCULATE(SUM('YourTable'[total tasks]),
FILTER('YourTable', 'YourTable'[task date] = PreviousDate))
RETURN
IF(ISBLANK(PreviousTotalTasks), 0, PreviousTotalTasks)

 

This formula calculates the "Previous total tasks" by looking for the "total tasks" on the day before the "task date" and returns 0 if there are no records for the previous day.

Make sure to replace 'YourTable' with your actual table name.

With this new column in place, your output table will match your expected output with the "Previous total tasks" included.

Thank you for your input . One doubt , in the below DAX ,have you created another column for "Previous task date " . Kindly share the DAX .   Modified_date = IF(
NOT ISBLANK([task date]) && NOT ISBLANK([Previous task date]),
IF([task date] > [Previous task date], [task date], [Previous task date])
)

I apologize for any confusion. In the provided DAX formula, it doesn't create a new column for "Previous Task Date." Instead, it's a measure that calculates the "Modified Date" for each record based on the "Task Date" and "Previous Task Date" in your data. If "Task Date" is greater than "Previous Task Date," it takes "Task Date" as the "Modified Date"; otherwise, it uses "Previous Task Date" as the "Modified Date."

 

Here's the DAX formula for creating a "Modified Date" measure:

 

Modified Date =
IF(
NOT ISBLANK([Task Date]) && NOT ISBLANK([Previous Task Date]),
IF([Task Date] > [Previous Task Date], [Task Date], [Previous Task Date]),
BLANK()
)

 

This measure calculates the "Modified Date" based on the conditions you provided. If both "Task Date" and "Previous Task Date" are not blank, it compares the two dates and selects the greater date as the "Modified Date." If either of the dates is blank, it returns a blank value.

You can use this measure in your Power BI report to display the "Modified Date" for each record based on the logic you described.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.