Group previous years based on selected year

marcvd — Wed, 28 Feb 2024 19:39:04 GMT

Hi all

I would like to summarise / group by years in a table based on a selected year.

Have data for 2020, 2021, 2022 and 2023.

Three columns voucher, date and amount.

When I select 2023 I would like to show the 2023 vouchers in detail (with voucher and date) and at the top the summary of the previous year (one row, only the amount).

Is this possible?

Thanks in advance!

Re: Group previous years based on selected year

Anonymous — Thu, 29 Feb 2024 06:07:35 GMT

Hi, marcvd

Based your infomation, I create a table:

You can try using DAX with visuals. If we don't have a Year column, we can create a Year column first, and then put the new column into the slicer.

Year = YEAR('Table'[Date])

Create a Measure called Previous Year Summary and put it in the card visual to show the total revenue for the previous year for the selected year.

DAX:

Previous Year Summary = CALCULATE( SUM('Table'[Amount]), FILTER( ALL('Table'), 'Table'[Year] < MAX('Table'[Year])&& YEAR('Table'[date]) = YEAR(MIN('Table'[date])) - 1 ) )

Then put all the information of the voucher into another table. Here's my preview:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

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

topic Group previous years based on selected year in DAX Commands and Tips

Group previous years based on selected year

Re: Group previous years based on selected year