Forum Discussion

snoozee's avatar
snoozee
Regular Visitor
4 years ago

Task Completed Daily

Hi   I'm trying to work out DAX for the following and I think I have to create a new table ?   I need to know whether a certain task has been completed on daily basis by every person, so in the e...
v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi snoozee ,

 

Please try the calaulated table.

 

NewTable = 
ADDCOLUMNS (
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( 'Table'[Name] ), VALUES ( 'Table'[Date] ) ),
        "Task",
            CALCULATE (
                MAX ( 'Table'[Task] ),
                FILTER (
                    'Table',
                    'Table'[Name] = EARLIER ( [Name] )
                        && 'Table'[Date] = EARLIER ( [Date] )
                )
            )
    ),
    "Completed", IF ( [Task] = "A", "Y", "N" )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • snoozee's avatar
    snoozee
    Regular Visitor
    4 years ago

    Hi

     

    I'm struggling to get this to work and unfortunately can't open your attachment as I can't upgrade my May 2021 version of Power BI as if I do I can't save to our server 😞

     

    Table = 
ADDCOLUMNS(
ADDCOLUMNS(
CROSSJOIN( VALUES('L_Teller_BalanceTransaction'[BranchCode]), VALUES('L_Teller_BalanceTransaction'[BalanceDatetime].[Date])),
"BalanceType",
CALCULATE(
MAX('L_Teller_BalanceTransaction'[BalanceType]),
FILTER('L_Teller_BalanceTransaction',
'L_Teller_BalanceTransaction'[BranchCode]=EARLIER('L_Teller_BalanceTransaction'[BranchCode]) && 'L_Teller_BalanceTransaction'[BalanceDatetime].[Date] =EARLIER('L_Teller_BalanceTransaction'[BalanceDatetime].[Date])
))),
"Completed",IF([BalanceType] = "BULK","Y","N" ))

     

    It's not showing the correct results

    The completed column in the image below is correct at "N" based on the BalanceType, however when I filter the date by 8th March BalanceType BULK isn't appearing, yet branchcode 00085014 on the 8th March did complete a BULK balance so can't work out why it's not appearing 😞