Last Year Total in Matrix

[Anonymous]

Hi,

I have used Matrix to have a YTD reporting in my PBI report and it is great to see the current year total can be easily displayed.

My question is, if my data set is like below in the second pic. How can I add an extra column right next to the current year total to show last year's total. 

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create two measures as below.

Current cumulative = 
var m = SELECTEDVALUE('Table'[MonthNum],12)
return
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALLEXCEPT('Table','Table'[Type]),
        YEAR([Date])=YEAR(TODAY())&&
        'Table'[MonthNum]<=m
    )
)

Last cumulative = 
var m = SELECTEDVALUE('Table'[MonthNum],12)
return
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALLEXCEPT('Table','Table'[Type]),
        YEAR([Date])=YEAR(TODAY())-1&&
        'Table'[MonthNum]<=m
    )
)

Result:

Best Regards

Allan

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

[Anonymous]

Thanks for your help. Just wonder if there's a way to just show the total for the whole year and not each month?

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may remove 'Month' from 'Column'. Here is the result.

Best Regards

Allan

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

[parry2k]

@Anonymous so it should be simple measure like this:

LY  = CALCULATE ( SUM ( Table[Amount] ), DATEADD ( DimDate[Date], -1, YEAR ) )

in table visual, drop date from date dimension and LY and TY measures and you will get the result.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

I have tried this way however it gives me extra things which I don't really need as you can see below (1) LY comparison for every month (2) "LYSP 000's" has include all year data to compare a YTD data

[amitchandak]

@Anonymous , Try a new column like

LY =
var _type = ([Type])
var _dt = [Date]
return
sumx(filter(Table, [Type] =_type && [Date] = date(year(_dt)-1, month(_dt), day(_dt))),[TY])

[parry2k]

@Anonymous Do you have a date dimension in your model? i

[Anonymous]

Yes like below.

[parry2k]

@Anonymous why you want a column for LY if the same can be achieved using Measure and which is the recommended way.

[Anonymous]

The "column" I mean to create one to display LY total which can show up right next to the total in column in the first snip.