Forum Discussion

marcvd's avatar
marcvd
Frequent Visitor
2 years ago

Group previous years based on selected year

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!

M

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.