topic Re: MAXDate and Maxdate)lastyear with value in DAX Commands and Tips

MAXDate and Maxdate)lastyear with value

RasmusN532 — Wed, 08 May 2024 13:10:43 GMT

Hi,

I am trying to make a KPI card which shows how much we invoiced today and sameday last year. This it works. (see measure below).

As you can see below this is what the KPI looks like.

Right now same day last year shows blank as last year, now this day was weekend(see day 8 in example below) . However I want it to show 90 since its the max day last year that had a value (I dont want to show later than 2023-05-08)

IRevenue MAX (This Year) = CALCULATE([Revenue],FILTER(calendar,calendar[invoice_date]=(max(calendar[invoice_date]))))

IRevenue MAX (Last Year) = CALCULATE([Revenue],FILTER(calendar,calendar[invoice_date]=(max(calendar[invoice_date])-365)))

Thanks in advance!

Re: MAXDate and Maxdate)lastyear with value

lbendlin — Wed, 08 May 2024 23:23:57 GMT

365 is wrong. Same day last year is 364.

Re: MAXDate and Maxdate)lastyear with value

RasmusN532 — Thu, 09 May 2024 08:39:27 GMT

Well I had 365 more of an example, and isnt it 366 since its leap year? Anyway thats not the issue here, I need it to look for the "highest date" last year not after this years maxdate, that has a value

Kind regards,

Re: MAXDate and Maxdate)lastyear with value

lbendlin — Thu, 09 May 2024 11:23:51 GMT

"Same day" is always 364 (52*7) regardless of leap year.

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: MAXDate and Maxdate)lastyear with value

Anonymous — Mon, 20 May 2024 07:20:50 GMT

Hi RasmusN532 ,

Thanks for the reply from lbendlin .

Please try modifying your measure syntax as follows:

IRevenue MAX (Last Year) = VAR_a = MAX(calendar[invoice_date]) VAR SameDayLastYear = DATE ( YEAR ( _a ) - 1, MONTH ( _a ), DAY ( _a ) ) VAR_b = CALCULATE ( MAX ( calendar[invoice_date] ), calendar[invoice_date] <= SameDayLastYear, NOT (ISBLANK ([Revenue] ) ) ) RETURN CALCULATE ( [Revenue], FILTER ( calendar, calendar[invoice_date] = _b ) )

The logic of this measure is:

Start by determining the current date of the current year.

Count the same day last year.

Finds the same or previous date in the last year that had revenue that was not null.

Calculate the revenue for the corresponding date based on the found date.

If this modified syntax does not help you solve the problem, please provide me with the pbix file. Remember not to log in to the current account when uploading files to Power Bi Desktop.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!