Unable to calculate the correct previous year revenue

[shubhra992]

Hi All,

I am facing an issue with my DAX expression to calculate the previous year revenue for all the contracts. I have in my table the invoice date for each cintract and calculated the previous year invoice date (mentioned as previous year). The DAX is working well if the contracts are still ongoing. However, if a contract ended the previous year, I am unable to calculate the correct previous year revenue as the expression is rejecting all the ccontracts that ended last year. I have posted my query below. Could you please help me identify the problem.

Thank you

Previous Rev =

Var _Prevdate= 'evenue'[Previous year invoice date]

VAR _MonthStartPrevDate = STARTOFMONTH('Revenue'[Previous year invoice date])

Return

CALCULATE(SUM('Revenue'[Revenue]),FILTER('Revenue','Revenue'[Invoice Date] >= _MonthStartPrevDate && 'Revenue'[Invoice Date] <= _Prevdate && 'Waterfall Revenue'[Contract ID]=EARLIER('Revenue'[Contract ID])))

[Anonymous · Accepted solution]

Hi @shubhra992 ,

Please update the formula of calculated column as below and check if it can return the expected result...

Previous Rev =
VAR _Prevdate = 'Revenue'[Previous year invoice date]
VAR _MonthStartPrevDate =
    STARTOFMONTH ( 'Revenue'[Previous year invoice date] )
RETURN
    CALCULATE (
        SUM ( 'Revenue'[Revenue] ),
        FILTER (
            ALL ( 'Revenue' ),
            'Revenue'[Invoice Date] >= _MonthStartPrevDate
                && 'Revenue'[Invoice Date] <= _Prevdate
                && 'Revenue'[Contract ID] = EARLIER ( 'Revenue'[Contract ID] )
        )
    )

If the above one can't help you figure out, please provide some raw data in your table 'Revenue' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards