topic Re: DAX formula for previous year (numbers not in date format) in DAX Commands and Tips

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

Mendy_4580 — Thu, 25 Jan 2024 13:30:06 GMT

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!

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

lbendlin — Thu, 25 Jan 2024 22:32:33 GMT

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-Forum/ba-p/963216

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/1447523

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

Anonymous — Fri, 26 Jan 2024 08:36:47 GMT

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.