Show current and last year calculated column

asl10 — Wed, 18 Jan 2017 10:45:06 GMT

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

Re: Show current and last year calculated column

technolog — Mon, 28 Aug 2023 21:03:30 GMT

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.

topic Re: Show current and last year calculated column in DAX Commands and Tips

Show current and last year calculated column

Re: Show current and last year calculated column