DAX formula for previous year (numbers not in date format)

[Mendy_4580]

Hello,

I am new to Power Bi and DAX writting, so please help me.

I want to create a simple formula (or I though it was simple), where the amount I see should be the previous year total amount. Example: if year is 2020, the number I see should be 2019 result.

I tried with the following formula:

Y-1 = CALCULATE([Total],Mapping_Year[Year]-1)

but the result is just the total for the respective year (not the previous year), see picture below:

How can I fix the formula? Also, my [Year] information is stored as number (not as date), so the DATEADD formula doesn't work.

Thank you!

[Anonymous · Accepted solution]

Hi @Mendy_4580 

Maybe you can try this:

I create a set of sample data and a matrix:

Then create a measure:

Measure =
    VAR _currentdate = MAX('Table (2)'[date])
//get the current date of this row in the matrix
    VAR _Previousdate = CALCULATE(
        MAX('Table (2)'[date]),
        FILTER(
            ALLSELECTED('Table (2)'),
            'Table (2)'[date] < _currentdate
        )
    )
//get the date of previous row in the matrix
    RETURN
        CALCULATE(
            SUM('Table (2)'[value]),
            FILTER(
                ALLSELECTED('Table (2)'),
                'Table (2)'[date] = _Previousdate
            )
        )
//get the value sum of the previous date

The result is as follow:

Best Regards,

Zhengdong Xu

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...