Dynamically Changes affect the Matrix columns

[jismongeorge63]

Can anyone please help me achieve this?

1) List the Customers and their corresponding Sales in a Matrix for a period of 4 months
a. These Customers SHOULD have sales for the CURRENT MONTH and at the same time SHOULD NOT have any sales for all of the previous months(3 months)

2) List the Customers and their corresponding Sales in a Matrix for a period of 4 months
a. These Customers SHOULD NOT have sales for the CURRENT MONTH and at the same time SHOULD have sales for all of the previous months(3 months)

In both cases, the CURRENT MONTH would be selected by the user and according the user selection the report should dynamically reflect the changes

[Anonymous · Accepted solution]

Hi,@jismongeorge63 .Hello,@amitchandak ,

thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
I am glad to help you.

According to your description, you want to select a month through the slicer, and display the data of the previous three months in visual without that month.

If I understand you correctly, you can refer to the following test I did below

This is my test data

I constructed a date table, Dates, to use as a slicer (which has nothing to do with the table:"case03")

I have created a measure, which marks out the dates that are in the first three months of the month selected by the slicer (excluding the month selected by the slicer), and marks it as 1 if it is satisfied, and 0 otherwise.

M_result = 
VAR dateValues=MAX('Dates'[Date_])
VAR previousThreeMonthStart=EOMONTH(dateValues,-4)+1
VAR previousThreeMonthEnd=EOMONTH(dateValues,-1)

RETURN 
IF(MAX('case03'[Date])>=previousThreeMonthStart&&MAX('case03'[Date])<=previousThreeMonthEnd,1,0)

Modify the filter criteria in the Filters field: when filtering by M_result, only data with M_result of 1 will be filtered.

like this.

If you want to include the selected month, you can use the following measure: M_includeSelected

M_includeSelected = 

VAR dateValues=MAX('Dates'[Date_])
VAR previousThreeMonthStart=EOMONTH(dateValues,-4)+1
RETURN 
IF(MAX('case03'[Date])>=previousThreeMonthStart&&MAX('case03'[Date])<dateValues,1,0)

like this:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[amitchandak]

@jismongeorge63 ,

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) //Current Month

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth( MAX('Date'[Date]), -1) ,-3,MONTH))

CUstomer Active in current not in last three

counts(Values(Customer[Customer]), if(not(isblank([MTD Sales]))  && isblank([Rolling 3]) , [Customer], Blank() ) )

Similar to

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

[jismongeorge63]

If user click the one month in sliver, the columns in Matrix shows previous 3 month. For example, for April , shows March,Feb , Jan . for may , apr,March,Feb etc..

[jismongeorge63]

Name, date and sales are the columns in data