cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
julesdude
Helper V
Helper V

Finding the Last Value for Last/Previous Year in DAX

Hi there,

 

I need help with something that in theory should be quite simple to accomplish, but although I've seen similar examples, not anything quite the same.

 

SOURCE DATA

I have a table loaded into my data model with a list of transactions which are date stamped in one column. Another column provides the value of the transaction, and a third provides a asset reference ID.

 

REPORT CANVAS

On the report canvas, I've created a table where rows begin by asset reference ID. In the second column I want to provide the value of the last transaction in the current year for that asset reference ID (this may be the same as last year if we have not had an updated value for this year yet). The third column needs to contain the last value provided in the previous calendar year. 

 

Obviously the first column is quite easy, and I have all my asset reference IDs listed. The second column I found the following DAX to do the trick:

 

Asset Value TY = lastnonblankvalue(Asset_Value[Date], max(Asset_Value[Value]))

 

However, I am struggling on how I would adapt this (or completely different approach) to find the last value provided for the previous calendar year for the third column.

 

Can anyone give a helping hand?

11 REPLIES 11
v-kalyj-msft
Community Support
Community Support

Hi @julesdude ,

According to your description, I create a sample.

vkalyjmsft_0-1658401600501.png

From the above snapshot can see, the Asset Value TY for Referece ID1 is 19, for Referece ID1 is 18, the Asset Value LY for Referece ID1 is 11, for Referece ID1 is 12. Here's my solution.

Create two measures.

Asset Value TY =
LASTNONBLANKVALUE ( Asset_Value[Date], MAX ( Asset_Value[Value] ) )
Asset Value LY =
CALCULATE (
    [Asset Value TY],
    FILTER (
        ALLEXCEPT ( 'Asset_Value', 'Asset_Value'[Reference ID] ),
        YEAR ( 'Asset_Value'[Date] )
            = YEAR ( TODAY () ) - 1
    )
)

Get the correct result.

vkalyjmsft_1-1658401822079.png

For the matrix you mentioned, put the Asset Value TY in the Values works fine.

vkalyjmsft_2-1658401857893.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @v-kalyj-msft 

Thanks very much for your detailed explanation. It almost worked perfectly - it did not work for me at first and gave me the following table:

 

julesdude_0-1658504127076.png

 

The columns should be by year, not the same year repeating whenever there is an update for the asset value, which was happening here.
So, I went back into my data model view. I have the Asset_Value[Date] field linking in with my bespoke Date Table (to the Date key/Primary Key). So I broke that link and the Asset_Value(Date) field in the Fields pane in the report view became a date hierachy again (Power BI inbuilt one). When I used the Year from this it gave me what I wanted:

julesdude_1-1658504740736.png

 

This is a lot better - it's providing just one year per column and if there are multiple entries of a value for a given year, it just takes the latest value in that year and displays it here.

BUT - it is still not quite right. You'll notice at the bottom, the total is now providing the total of the single highest figure in that year (or in that column) - it is not adding all the totals in the column together. How can this be achieved?
And also, why was it not working previous when, as I mentioned above, Asset_Value[Date] was linked to my bespoke date table?

Hi @julesdude ,

In order to get the correct value on the total row, I create a new measure.

Measure =
VAR _T =
    ADDCOLUMNS (
        'Asset_Value',
        "TY",
            VAR _V =
                MAXX (
                    FILTER (
                        ALL ( 'Asset_Value' ),
                        'Asset_Value'[Reference ID] = EARLIER ( 'Asset_Value'[Reference ID] )
                            && YEAR ( 'Asset_Value'[Date] ) = YEAR ( EARLIER ( 'Asset_Value'[Date] ) )
                            && 'Asset_Value'[Date]
                                = MAXX (
                                    FILTER (
                                        ALL ( 'Asset_Value' ),
                                        YEAR ( 'Asset_Value'[Date] ) = YEAR ( EARLIER ( 'Asset_Value'[Date] ) )
                                            && 'Asset_Value'[Reference ID] = EARLIER ( 'Asset_Value'[Reference ID] )
                                    ),
                                    'Asset_Value'[Date]
                                )
                    ),
                    'Asset_Value'[Value]
                )
            VAR _C =
                COUNTROWS (
                    FILTER (
                        'Asset_Value',
                        'Asset_Value'[Reference ID] = EARLIER ( 'Asset_Value'[Reference ID] )
                            && YEAR ( 'Asset_Value'[Date] ) = YEAR ( EARLIER ( 'Asset_Value'[Date] ) )
                    )
                )
            RETURN
                DIVIDE ( _V, _C )
    )
RETURN
    SUMX ( _T, [TY] )

Put the measure in the matrix values, get the correct result.

vkalyjmsft_0-1658666606940.png

As for why there is an error when using another date table, I think it may be that the date range between the two tables is different, try to use the date column in this table to see if it can be achieved.

 

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-kalyj-msft thank you so much for this - I had thought it was working and the figures showing correctly, but on second glance it appears they are not accurate.

My table now looks like this:

julesdude_0-1658698140889.png

 

There is some repetition of values for each asset. I checked and they are actually different.
I think as well that for when there is a blank in the year, it doesn't mean the asset has no value and should not be used for the total of the year, it should probably take the total from the last year where there is a value, even though sometimes a value hasn't been provided for that asset in that year.

Is there a way of enhancing the above DAX you've provided that reverts to the last value where needed to be used in that year's total? And I'm not sure why the values are displaying differently now. What we had in the previous values/measure was correct.

Thank you so much again with you help in this!

Hi @julesdude ,

According to your description, modify the formula to:

Measure =
VAR _T =
    ADDCOLUMNS (
        'Asset_Value',
        "TY",
            VAR _V =
                MAXX (
                    FILTER (
                        ALL ( 'Asset_Value' ),
                        'Asset_Value'[Reference ID] = EARLIER ( 'Asset_Value'[Reference ID] )
                            && YEAR ( 'Asset_Value'[Date] ) = YEAR ( EARLIER ( 'Asset_Value'[Date] ) )
                            && 'Asset_Value'[Date]
                                = MAXX (
                                    FILTER (
                                        ALL ( 'Asset_Value' ),
                                        YEAR ( 'Asset_Value'[Date] ) = YEAR ( EARLIER ( 'Asset_Value'[Date] ) )
                                            && 'Asset_Value'[Reference ID] = EARLIER ( 'Asset_Value'[Reference ID] )
                                    ),
                                    'Asset_Value'[Date]
                                )
                    ),
                    'Asset_Value'[Value]
                )
            VAR _C =
                COUNTROWS (
                    FILTER (
                        'Asset_Value',
                        'Asset_Value'[Reference ID] = EARLIER ( 'Asset_Value'[Reference ID] )
                            && YEAR ( 'Asset_Value'[Date] ) = YEAR ( EARLIER ( 'Asset_Value'[Date] ) )
                    )
                )
            RETURN
                DIVIDE ( _V, _C )
    )
RETURN
    IF (
        ISINSCOPE ( 'Asset_Value'[Reference ID] ),
        [Asset Value TY],
        SUMX ( _T, [TY] )
    )

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-kalyj-msft 

Thanks so much again.

Unfortunately, I still get repetition in numbers:

 

julesdude_0-1658846358293.png

 

Hi @julesdude ,

Do you put the row of the matrix in the ISINSCOPE function (in my case, it's Reference ID)? As you say, the measure [Asset Value TY] can get correct result except the total row, but the new measure can get the correct total, in this way, the ISINSCOPE function will seperately calculate them and both return correct result. In my sample, it did work. Please check and analyze again the difference between your sample and mine, if it still not work, I may need to look specifically in the file.

vkalyjmsft_0-1658885021093.png

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-kalyj-msft 

I created a new example based on the dataset I'm working on - it is very close and I have kept the referenced fields required and modified the data slightly.

I attach the sample file here.

I have transfered the DAX you have provided - thank you.

Two things:

1. Rows - Yes I get values for an asset in their corresponding year columns - the repetition is gone which is great, but the visible value is not the very latest dated value available against that asset in the source table - it needs to be the latest value of that year that is visible - and that value is used in the total.

2. Totals - If there is no value against an asset for a year, and for that year is is blank in the table (so no record in the source table in that year), it should remain blank in the row, but the total should then consist of the last previous value for that asset. So that could be in the year before, or even 2 years before, whatever is latest.

Example 

I should add that I'd also be open to creating a table in Power Query, merging queries/tables in it or adding additional columns to existing tables if that might provide an easier route to accomplishing the above and minimise the complexity of the DAX.

Whitewater100
Super User
Super User

Hi:

If you can add a date table with relatiionship to your AssetValue[Date] field ad mark as date table. I'll put one way to obtain a date table via DAX at the bottom. This lets you do time-intel calculations.

Last Value LY = CALCULATE([Asset Value TY],PREVIOUSYEAR(Dates[Date]))
 
Date Table
MODELING>NEW Table. Name = Dates

DATES =

  GENERATE (

    CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),

    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday   

    VAR currentDay = [Date]

    VAR days = DAY( currentDay )

    VAR months = MONTH ( currentDay )

    VAR years = YEAR ( currentDay )

    VAR nowYear = YEAR( TODAY() )

    VAR nowMonth = MONTH( TODAY() )

    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1

    VAR todayNum = WEEKDAY( TODAY() )

    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )

  RETURN ROW (

    "day", days,

    "month", months,

    "year", years,

    "day index", dayIndex,

    "week index", weekIndex,

    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),

    "year index", INT( years - nowYear )

  )

)

I hope this worksa for you. One note this method is picking up the largest value for the year. If you have multiple entries,say on Dec 31st, it will just pick the largest of all your Dec 31st entries.

 

I hope this helps!

Hi @Whitewater100 and many thanks for responding.

I actually was in the process of putting in a created date table using Power Query, so I now have that created in the model and for every date that shows in my other tables I've linked these as date table 1----* other tables relationship.
However, it's created a problems with my couple of my tables.

Table 1:

Asset Ref  Value LY Value TY
aaaa[blank]40
bbb[blank]60

 

Columns:

Asset Ref

Value LY [DAX]:

 

CALCULATE([Value TY],PREVIOUSYEAR(Asset_Value[Value Date]))

 

Value TY [DAX]:

 

lastnonblankvalue(Asset_Value[Value Date], max(Asset_Value[Value]))

 

As you can see, the Value LY DAX is returning blanks.


 Table 2

 

Asset Ref  2019 2020 2021 2022
aaaa40    
bbb60    

 

This matrix table is using the following:

Rows:

Asset Ref

 

Columns:

Dates Hierarchy

   Year

 

Values:

Asset Value TY

 

But, although I'd expect the latest value in the year categories to appear, they are all blank, and is in a blank column on its own.

How do I fix this? I thought that linking to the bespoke Date Table I created would align the date provided in the Value Date to the Year categorisation in the calendar table?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors