Clustered Bar Chart to Show the Last 2 year data based on selected year slicee

[rmateo]

I have a date table connected to the dates in a fact table containing total invoice. I want to show the total invoice comparison for each month of the selected year and the year before the selected year to make it dynamic. Tried DAX to sum the total invoice using the selected year and selected year -1 as filter and disabled slicer interaction on the chart but it is still showing all the years in the chart. 

[vivek31]

HI @rmateo ,

you can find first selectedyear value to use this formula

select year = CALCULATE(SUM(Sales[SalesAmount]),
                        YEAR(Sales[DateKey]) = SELECTEDVALUE('Calendar 2'[Year]))

and second find selectprevious year value to use this formula.

select previous year = 
VAR year_ = SELECTEDVALUE('Calendar 2'[Year])
VAR result = CALCULATE(SUM(Sales[SalesAmount]),YEAR('Calendar 2'[DateKey]) = year_ - 1)
RETURN
result

 and apply clustered colum chart

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

[rmateo]

tried these DAX formulas:

TotalInvoiceLast2Years =
VAR SelectedYear = SELECTEDVALUE(dimDate[Year])
RETURN
CALCULATE(
SUM(fctRevenue[Invoiced (USD)]),
REMOVEFILTERS(dimDate),
dimDate[Year] IN {SelectedYear, SelectedYear - 1}
)

TotalInvoiceLast2Years =
VAR SelectedYear = SELECTEDVALUE(dimDate[Year])
RETURN
CALCULATE(
SUM(fctRevenue[Invoiced (USD)]),
FILTER(
ALLSELECTED(dimDate),
dimDate[Year] = SelectedYear || dimDate[Year] = SelectedYear - 1
)
)

TotalInvoiceLast2Years =
VAR SelectedYear = SELECTEDVALUE(dimDate[Year])
RETURN
CALCULATE(
SUM(fctRevenue[Invoiced (USD)]),
FILTER(
REMOVEFILTERS(dimDate),
dimDate[Year] = SelectedYear || dimDate[Year] = SelectedYear - 1
)
)

[Anonymous]

Hi, @rmateo

I am glad to help you.

First you need to make sure the date table is labeled as such.
Then you can try to change the DAX to the following:

TotalInvoiceLast2Years =
VAR SelectedYear = SELECTEDVALUE(dimDate[Year])
RETURN
CALCULATE(
    SUM(fctRevenue[Invoiced (USD)]),
    FILTER(
        ALL(dimDate),
        dimDate[Year] = SelectedYear || dimDate[Year] = SelectedYear - 1
    )
)

If the above does not solve the problem you are experiencing, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BeaBF]

@rmateo Hi! can you share the pbix or paste some sample data and your dax measures?

BBF