How to filter latest month data by customer

[Anonymous]

Hi ,

I wanted to filter the view by latest Month of each customer.

I have 2 customer 

Customer A has data till December 2021  

Customer B has data till November 2021

when i filtered by each customer it should show latest month of it.

Kindly help me to resolve this

[amitchandak]

@Anonymous , if you have date  , you should be able to get using

Format(max('Date'[Date]), "mmm-YYYY")

[Anonymous]

@amitchandak 

please understand the question and your reply wont work.

[amitchandak]

@Anonymous , I checked back again, sorry not able to get it. Your subject is not in line with the description. So, Can you share sample data and sample output in table format?

[Anonymous]

I have 2 column 

 customer code       Date

ABC                     Dec 2021

ABC                     Nov 2021

XYZ                      Nov 2021

I wanted customer code filter act as global filter and date filter should pass through the customer filter

when i filtered by customer code it should filter the latest month data

for example  when i filter  'XYZ'  it should show Nov data

[amitchandak]

@Anonymous , See if this measure can help, as the customer is from the same table or linked table, so it should filter. Assuming no date is filtered

new measure =
var _max = maxx(allselected(Table),Table[Date])
var _min = eomonth(_max,-1)+1
return
calculate( sum(Table[Value]), filter(Table, Table[Date] >=_min && Table[Date]<=_max))

[Anonymous]

Measure = var _max = maxx(allselected('DISCOPrime CustomerRiskFactorsSnapshot'), 'DISCOPrime CustomerRiskFactorsSnapshot'[date])

var _min = eomonth(_max,-1)+1

return

calculate( COUNT('DISCOPrime CustomerRiskFactorsSnapshot'[BoeingPartNumber]), filter('DISCOPrime CustomerRiskFactorsSnapshot', 'DISCOPrime CustomerRiskFactorsSnapshot'[date] >=_min && 'DISCOPrime CustomerRiskFactorsSnapshot'[date]<=_max))

Customer code   date   part count

ABC           Dec 2021    4096       but i'm getting overall count i.e 22123  its giving total

I want only Dec 2021 data for customer ABC

XYZ  gettting correct number since it has only Nov data

[v-chenwuz-msft]

Hi @Anonymous ,

I have two ways. result by measure or filter by measure.

Prerequisites: create a CustomerTable by code :

CustomerTable = VALUES('Table'[Customer])

1 result by measure.

code:

Measure_part_count =
VAR _A =
    CALCULATE( MAX( 'Table'[Date] ), ALLEXCEPT( 'Table', 'Table'[Customer] ) )
VAR _PartCount =
    SUM( 'Table'[part count] )
RETURN
    IF(
        ISFILTERED( 'CustomerTable'[Customer] ),
        IF( MAX( 'Table'[Date] ) = _A, _PartCount, BLANK() ),
        _PartCount
    )

result:

2 filter by measure.

measure code :

Measure_in_filters =
VAR _A =
    CALCULATE( MAX( 'Table'[Date] ), ALLEXCEPT( 'Table', 'Table'[Customer] ) )
VAR _PartCount = 1
RETURN
    IF(
        ISFILTERED( 'CustomerTable'[Customer] ),
        IF( MAX( 'Table'[Date] ) = _A, _PartCount, BLANK() ),
        _PartCount
    )

Put the measure in filters on this visual and set item is 1

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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