MAXDate and Maxdate)lastyear with value

[RasmusN532]

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!

[v-huijiey-msft · Accepted solution]

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!

[lbendlin]

365 is wrong.  Same day last year is 364.

[RasmusN532]

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,

[lbendlin]

"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-...

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...