Day over day change with specific conditions

[IvanS]

Hi guys,

I have Power Automate flow that is storing actual data from Power BI dataset on daily basis and saving it into Excel table.

The format of table is following with desired calculation in the last column:

Mailbox	Folder Path	Nr. of Emails	Upload Date	Daily Change (desired)
ABC@company.com	\Inbox\In Progress	20	26.3.2024	0 (as there is no entry with previous date)
XYZ@company.com	\Inbox\	10	26.3.2024	0 (as there is no entry with previous date)
XYZ@company.com	\Inbox\In Progress	3	26.3.2024	0 (as there is no entry with previous date)
ABC@company.com	\Inbox\In Progress	25	27.3.2024	5
XYZ@company.com	\Inbox\	8	27.3.2024	-2
XYZ@company.com	\Inbox\In Progress	6	27.3.2024	3

The calculation should take into consideration the Mailbox and Folder Path and show the daily change for this unique combination.

I tried with something like below, but the calculation is not working.

VAR _previousday = MAX(Upload date) - 1

VAR _currentday = MAX(Upload date)

VAR _previousdayemails = 
CALCULATE(SUM(Nr. of Emails), Upload date = MAX(Upload date) - 1 )

VAR _currentdayemails =
CALCULATE(SUM(Nr. of Emails), Upload date = MAX(Upload date) )

RETURN
_currentdayemails - _previousdayemails

Thank you for any help!

IvanS

[v-yilong-msft · Accepted solution]

Hi @IvanS ,

I create a table as you mentioned.

Then I create a calculated column to satisfy your requirements. Here is the DAX code.

Daily Change = 
VAR _previousDate =
    CALCULATE (
        MAX ( 'Table'[Upload Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Upload Date] < EARLIER ( 'Table'[Upload Date] )
        )
    )
VAR _previousNr =
    CALCULATE (
        MAX ( 'Table'[Nr. of Emails] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Mailbox] = EARLIER ( 'Table'[Mailbox] )
                && 'Table'[Folder Path] = EARLIER ( 'Table'[Folder Path] )
                && 'Table'[Upload Date] = _previousDate
        )
    )
RETURN
    IF ( _previousNr <> BLANK (), 'Table'[Nr. of Emails] - _previousNr, 0 )

Finally you will get what you want.

Best Regards

Yilong Zhou

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