Filter Customers based on current year sales segment

[Varshi288]

Hello Community,

 I need help in the dax to filter customers based on sales range in the current year (2022) and to show sales of previous years as shown in the table. With the below dax Im getting error as attached in the snapshot. 

TP Segment = VAR _TP_Value =CALCULATE(MAX(Data[TP $]),FILTER(ALL(Data),Data[Customer HQ] =SELECTEDVALUE(Data[Customer HQ])),Period[Year]=YEAR(TODAY()))

            VAR _current_tp_ouput = IF(_TP_Value>=50000 && _Tp_value<=100000,_Tp_value,BLANK())

            RETURN

            IF(_current_tp_ouput<>BLANK(),MAX(Data[TP $]),BLANK())

 Slicer selected 100k t0 150k.

Customer	2020	2021	2022
AA1	50k	100k	150K
AA2	100K	150K	50K
AA3	200K	100k	250k
AA4	180k		120k
AA5	100k	120k	60k
Result
Customer	2020	2021	2022
AA1	50k	100k	150K
AA4	180k		120k

[v-shex-msft]

Hi @Varshi288 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Varshi288]

Hi  @Xiaoxin Sheng

That didnt work in my data model. In the measure Vallue 2022 year is hard coded. it should dynamically select the year i.e  in Jan 2023 it should take 2023.Here the sample  pbix.

https://drive.google.com/file/d/1VNQtS65nT9Zj3nvpOzhRlijYM4OEzE1x/view?usp=share_link

[v-shex-msft]

Hi @Varshi288,

You can try to use the following measure formula if it suitable for your requirement: (it will filter records based  on the what if parameter table selections)

TP Segment = 
VAR _TP_Value =
    CALCULATE (
        MAX ( Data[TP $] ),
        ALL ( Data ),
        VALUES ( Period[Year] ),
        VALUES ( Customer[Customer] )
    )
VAR selection =
    ALLSELECTED ( Parameter[Parameter] )
VAR _start =
    MINX ( selection, [Parameter] )
VAR _end =
    MAXX ( selection, [Parameter] )
VAR _current_tp_ouput =
    IF ( _TP_Value >= _start && _Tp_value <= _end, _Tp_value, BLANK () )
RETURN
    IF ( _current_tp_ouput <> BLANK (), MAX ( Data[TP $] ) )

Regards,

Xiaoxin Sheng

[lbendlin]

First step is to unpivot your data.

Then you need to define what "current" year means for you.   This will be impacted by the filter context unless you tell it otherwise.

Let's assume you mean 2022 as you mention.  Create a calculated column of the 2022 value for each of your customers. (You cannot use a measure as measures cannot feed slicers)

Value 2022 = CALCULATE(sum('Table'[Value]),ALLEXCEPT('Table','Table'[Customer]),'Table'[Year]="2022")

Use this column for your slicer. 

Which gives you the desired result (albeit a different one than you indicated).

see attached