Forum Discussion
Condition filter based on values in current month and previous months
Thank you for your time to read the post. I have some difficulties creating this condition column. This report sample should list all customerIDs and weights shipped by Year/Month.
A condition column should indicates:
1. If [current month weight] is blank or zero, then "Has Not Bought"
2. If [current month weight] < [previous month weight] AND [previous month weight] < [previous previous month weight] , then "Down 2 Months"
3. If [current month weight] < [previous month weight] AND [previous month weight] > [previous previous month weight], then "Down 1 Month"
4. Elses are "Good"
The goal is to use this condition column to filter.
It should be like this:
I managed to find a solution. Please follow the steps.
1. Create a Calendar Table as
Calendar = CALENDAR (MINX('Main', [InvoiceDate]), MAXX('Main', [InvoiceDate]))
This creates continuous dates from min to max of the Invoicedate in Main table.
2. Create a measure to find the currentmonth based on the max invoicedate using
CurMonth = Month(Max('Calendar'[Date]))
3. Create a column called Month in Main Table as Month = Format(Main[InvoiceDate],"MMMM")
4. Create a column called MonthNo in Main Table as MonthNo = Month(Main[InvoiceDate])
5. Set the Month Column to be sorted by MonthNo
6. Create a summary table called CustWeights as
CustWeights = SUMMARIZE(Main,Main[Customer],Main[Month],"MonthNo",distinct(Main[MonthNo]),"TotWeight",sum(Main[Weight]))
7. Under the CustWeights table Create a measure called CurMonthWeight as
CurMonthWeight = Calculate(Sum([TotWeight]),Filter(CustWeights ,CustWeights [MonthNo]=[CurMonth]))
8. Under the CustWeights table Create a measure called PreviousMonthWeight as
PreviousMonthWeight = Calculate(sum([TotWeight]),Filter(CustWeights ,CustWeights [MonthNo]=[CurMonth] -1 ))
9. Under the CustWeights table Create a measure called Previous2MonthWeight as
Previous2MonthWeight = Calculate(Sum([TotWeight]),Filter(CustWeights ,CustWeights [MonthNo]=[CurMonth]-2))
10. Create another summarized table called CustLastBought as
CustLastBought= SUMMARIZE(CustWeights,CustWeights[Customer],"LastTransacted",Max(CustWeights[MonthNo]))
11. Add a column in this table
Description = If([LastTransacted]<>[CurMonth],"Has not Bought",
If([CurMonthWeight]<[PreviousMonthWeight] &&
[PreviousMonthWeight] < [Previous2MonthWeight] ,"DownTwoMonths" ,
If([CurMonthWeight]<[PreviousMonthWeight] &&
[PreviousMonthWeight] > [Previous2MonthWeight] ,"Down 1 Months" ,
"Good")
14. Using Manage Relationships create the relationship between tables.13. Now for the last Part
Create a Matrix table visual
With Customer from CustLastBought Table as the Row,
With Description from CustLastBought Table as the Row,
With Month from CustWeights as Columns,
With TotWeight from CustWeights as Values
and you should be able to see the results.
If this solves your issue please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
4 Replies
I managed to find a solution. Please follow the steps.
1. Create a Calendar Table as
Calendar = CALENDAR (MINX('Main', [InvoiceDate]), MAXX('Main', [InvoiceDate]))
This creates continuous dates from min to max of the Invoicedate in Main table.
2. Create a measure to find the currentmonth based on the max invoicedate using
CurMonth = Month(Max('Calendar'[Date]))
3. Create a column called Month in Main Table as Month = Format(Main[InvoiceDate],"MMMM")
4. Create a column called MonthNo in Main Table as MonthNo = Month(Main[InvoiceDate])
5. Set the Month Column to be sorted by MonthNo
6. Create a summary table called CustWeights as
CustWeights = SUMMARIZE(Main,Main[Customer],Main[Month],"MonthNo",distinct(Main[MonthNo]),"TotWeight",sum(Main[Weight]))
7. Under the CustWeights table Create a measure called CurMonthWeight as
CurMonthWeight = Calculate(Sum([TotWeight]),Filter(CustWeights ,CustWeights [MonthNo]=[CurMonth]))
8. Under the CustWeights table Create a measure called PreviousMonthWeight as
PreviousMonthWeight = Calculate(sum([TotWeight]),Filter(CustWeights ,CustWeights [MonthNo]=[CurMonth] -1 ))
9. Under the CustWeights table Create a measure called Previous2MonthWeight as
Previous2MonthWeight = Calculate(Sum([TotWeight]),Filter(CustWeights ,CustWeights [MonthNo]=[CurMonth]-2))
10. Create another summarized table called CustLastBought as
CustLastBought= SUMMARIZE(CustWeights,CustWeights[Customer],"LastTransacted",Max(CustWeights[MonthNo]))
11. Add a column in this table
Description = If([LastTransacted]<>[CurMonth],"Has not Bought",
If([CurMonthWeight]<[PreviousMonthWeight] &&
[PreviousMonthWeight] < [Previous2MonthWeight] ,"DownTwoMonths" ,
If([CurMonthWeight]<[PreviousMonthWeight] &&
[PreviousMonthWeight] > [Previous2MonthWeight] ,"Down 1 Months" ,
"Good")
14. Using Manage Relationships create the relationship between tables.13. Now for the last Part
Create a Matrix table visual
With Customer from CustLastBought Table as the Row,
With Description from CustLastBought Table as the Row,
With Month from CustWeights as Columns,
With TotWeight from CustWeights as Values
and you should be able to see the results.
If this solves your issue please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Anonymous
Thank you for your time and effort. In your step 11, the first "IF" clause, why do you write [LastTransacted] <> [CurMonth], "Has Not Bought"? I ask this because the result is not showing any "Has Not Bought". All the other IF clause are comparing weight, but the first one is comparing month number. Is this a typo or is intended? Thank you.
Never mind about the last post. I figured it out. But the description is still showing "Good" on those rows that suppose to be "Has Not Bought". I will keep tweaking. Thanks alot.
Got it right now. I changed first IF clause to IF([LastTransacted) < Max(CustWeight[MonthNo]), "Has Not Bought", .......
I don't know why yours is not working. Your LastTransacted value and CurMonth value are both 10. It should be good. Thank you very much.
