The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi Team,
I have one requirement . I am taking snapshot of daily data into the below table.
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
Location | group name | Previous task date | task date | previous total tasks | total tasks | Modified_date |
Location5 | groupC | 18-11-23 | 3 | 10/5/2023 | ||
Location1 | groupA | 21-10-23 | 24-11-23 | 3 | 10 | 10/6/2023 |
Location2 | groupA | 28-10-23 | 01-12-23 | 6 | 7 | 10/6/2023 |
Location3 | groupB | 04-11-23 | 22-11-23 | 10 | 1 | 10/6/2023 |
Location4 | groupC | 18-11-23 | 01-11-23 | 9 | 1 | 10/6/2023 |
Location6 | groupC | 18-11-23 | 2 | 10/6/2023 |
Kindly help
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))
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 .
Load your data into Power BI:
Create a new calculated column for "Modified_date":
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":
Modified Indicator = IF([total tasks] <> [previous total tasks], "Modified", "Unmodified")
Create a table visual with the desired columns:
Apply filters:
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.
User | Count |
---|---|
24 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
31 | |
12 | |
10 | |
10 | |
9 |