Histogram of the last three years

[QLB]

Hi,

I have a table containing product sales by year and by user, from 2010 to 2020 (just years, with "INT" format, not dates).
I have a dimension table with the list of distinct years available (i.e. from 2010 to 2020).
When a user chooses a year, I would like to display in a grouped histogram the sums of sales over the last three years.
For example, if a user selects "2019", the histogram displays the sums in 2017, 2018 and 2019.

I tried to use a segment, but (logically) only one year appears on the histogram.
I tried using two measures :
     N=selectedvalue(YEAR)
     Nm2=selectedvalue(YEAR)-2
then using a calculated table, filtering the year on the initial table from these measures, with no result.
I tried to use to directly filter the visual with these 2 measures, without result.

Can anyone help me?

Thanks

[QLB]

Hi @v-shex-msft ,

Thank you for your help and for your clarifications regarding the data levels. This explains my difficulties.
I did not manage to solve my problem with a DAX formula, but I worked around it by creating an additional table indicating the visible vintages according to the selected reference year.

For example, if the user selects the year 2019, the visuals will be filtered on the years 2019, 2018 and 2017.

Regards, 

QLB

[v-shex-msft]

Hi @QLB,

If you want to achieve custom filter refer, you need to create unconnected table as source of slicer. Then you can write a measure formula to extract the selections and compare with current date to return flag to use as a filter on your visual.

Applying a measure filter in Power BI - SQLBI

Regards,

Xiaoxin Sheng

[QLB]

Hi @v-shex-msft ,

Thank you for your reply.

I understand the usefulness of creating an unconnected table as a filter source and I am able to recover the selected data. As a reminder, the user only chooses one year.

I think I understand that I need to calculate a gap between the year filled in each row and the year selected in the segment, and use this gap in my visual (for example, filter gaps less than or equal to 2).
But I don't know how to calculate this difference, I can't find the right formula 😞
How compare a column value with a measure ? Should I use a calculated column or a measure ?

Can you enlighten me on this subject?

Thanks !

[v-shex-msft]

HI @QLB,

I think this scenario should be used measure formulas. AFAIK, current power bi does not support create dynamic calculated column/table based on filter effects. They do not work on the same level.

For compare value parts, you can use min/max function or selectedvalue function to get current date to compare with the table column in the filter conditions.

Using the SELECTEDVALUE function in DAX - SQLBI

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng