Forum Discussion

RasmusN532's avatar
RasmusN532
Helper I
2 years ago
Solved

MAXDate and Maxdate)lastyear with value

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!

 

"DAX help"
"Maxdate"
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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!