Classify values in a new column based on previous month value

Question

Hello;&nbsp;I have the following issue:&nbsp;I have a fact table with Date, Product, Company, Region and Value, and i need to add a column to classify the products based on the Month to Month or Year to Year variation. So in the end i will have a column with three possible values "Increased", "Decreased" and "No changes".&nbsp;&nbsp;I tried to do this with a SUMMARIZE table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Summarized_Table = SUMMARIZECOLUMNS(Fact_Table[Product],Fact_Table[Date],"Previous Month",PREVIOUSMONTH(Fact_Table[Date]),"Actual Value",SUM(Value))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and within that table added a calculated column for the previous month value of the product&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Previous Value = 
var Product = Summarized_Table[Product] 
var Date = Summarized_Table[Previous Month]
var PValue = CALCULATE(SUM(Fact_Table[Value]),Fact_Table[Date] = Date &amp;&amp; Fact_Table[Product] = Product)
return PValue&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and then adding the classify column to the summarized table:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Product_Status =
SWITCH(
    TRUE(),
    Summarized_Table[Actual Value] &gt; Summarized_Table[Previous Value],"Increased",
    Summarized_Table[Actual Value] &lt; Summarized_Table[Previous Value],"Decreased","No changes" 
)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Testing the summarized table, it works ok, it shows the correct data.Product_Status#ProductsActual ValuePrevious ValueIncreased12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200.40&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 190.50Decreased5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 120.30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 150.60No Changes1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.00&nbsp;But i need the Product_Status in the Fact Table so i can analyze by company and region.&nbsp;So what i do is add it the same i did with the summarized table:&nbsp;&nbsp;&nbsp;&nbsp;Product Status = 
var Product = Fact_Table[Product] 
var Date = Fact_Table[Date]
var Status = CALCULATE(MAX(Summarized_table[Product_Status]),Summarized_Table[Date] = Date &amp;&amp; Summarized_Table[Product] = Product)
return Status&nbsp;&nbsp;&nbsp;&nbsp;But when i test it in the fact table something weird happen, the "Previous Value" in the Fact Table is wrong:Product_Status#ProductsActual ValuePrevious ValueIncreased12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200.40&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 150.60Decreased5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 120.30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 190.50No Changes1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.00Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 420.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 441.10The Previous Value formula in the fact table is like this :&nbsp;Previous Value = CALCULATE(SUM(Fact_Table[Value]),PREVIOUSMONTH(Calendar[Date]))&nbsp;And when inspecting a single product the data shows this:Date: May 2022Product: PRODUCT AProduct_Status#ProductsActual ValuePrevious ValueIncreased&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12.00Decreased1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12.00&nbsp;Product A increased it's value, but that was in April22 vs March22 not in May22 so i can't understand why is not correctly assigned.&nbsp;&nbsp;

amitchandak · Accepted Answer

Anonymous , I would advice dynamic segmentation
&nbsp;
Diff = Sum(Table[Value]) - calculate(Sum(Table[Value]) , previousmonth(Date[Date]) )
&nbsp;
&nbsp;
Create a table with Start, end and bucket name
-9999 , 0, "Decreased"
0,0.00000001, "No Change"
0.00000001 , 99999, "Increased"
&nbsp;
&nbsp;
Note in case you do not want a range base bucket refer hwo can use a measure with text values, in the last video link
&nbsp;
Then you can create measures with help from this bucket table
countx(filter(values(Product[Product]) , [Diff] &gt;= min(bucket[Start])&nbsp; &amp;&amp; [Diff]&lt; Max(Bucket[end]) ),[Product])
&nbsp;
you need a bucket column in visual
&nbsp;
or try
sumx(Values( bucket[Bucket]) , calculate( countx(filter(values(Product[Product]) , [Diff] &gt;= min(bucket[Start])&nbsp; &amp;&amp; [Diff]&lt; Max(Bucket[end]) ),[Product])) )
&nbsp;
refer
Dynamic Segmentation Bucketing Binninghttps://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k
&nbsp;
text bucket - https://www.youtube.com/watch?v=EyL7KMw877Q
&nbsp;

Forum Discussion

Classify values in a new column based on previous month value

2 Replies

Related content

Getting value for previous year based on value name and year slicer

Previous Day Value

Previous Row Value Dax

Previous Day Value

DAX getting previous value

Product_Status	#Products	Actual Value	Previous Value
Increased	12	200.40	190.50
Decreased	5	120.30	150.60
No Changes	1	100.00	100.00