Show current and last year calculated column

[asl10]

Hi,

In a crystal report i have the following where i have a Year Group based on the below in order to see the revenue based on a user's input date on the reservation system and revenuew to come in based on customers reservation date. How can i replecated this with dax?

if {Rentals.Year} = Year (CurrentDate) then YEAR(CURRENTDATE)
else
if {Rentals.Input Date} < {@DateLastYear} and {Rentals.Year} = Year (CurrentDate)-1 then Year (CurrentDate)-1
else
0

**@DateLastYear is Year(CurrentDate)-1

[technolog · Accepted solution]

To replicate the logic in DAX, you'll want to create a calculated column. Before diving into the DAX formula, it's important to understand that DAX has slightly different functions and syntax than what you'd find in Crystal Reports formulas.

Here's how you might translate your logic into DAX:

Year Group =
VAR CurrentYear = YEAR(TODAY())
VAR LastYear = CurrentYear - 1
RETURN
IF (
Rentals[Year] = CurrentYear,
CurrentYear,
IF (
Rentals[Input Date] < DATE(LastYear, 1, 1) && Rentals[Year] = LastYear,
LastYear,
0
)
)
In the above DAX formula:

I'm using the TODAY() function to get the current date.
The YEAR() function extracts the year portion of a date.
The DATE() function creates a date from year, month, and day components.
You'd add this calculated column to your Rentals table (or an equivalent table in your Power BI model).

After creating the column, you can then use the "Year Group" column to create your desired visualizations in Power BI.