Get a Difference from Previous Month with several Categories

DAX DAX Calculations
1

Hi all,
I’ve been learning Power BI for only 2 weeks. I see great potential in this tools (thanks to enterprisedna), yet I still need to learn A LOT.

I need help getting the monthly difference of many products.

Here is the screenshot in excel: I want to change the left table into the right table.

image
image.png1953×441 21.8 KB

I have tried many times in Power BI, mostly using Measures to create the new column. Here is the formula I created, but it failed:

Diff FactoryA = calculate(CALCULATETABLE(Sheet2, Sheet2[ProductName], Sheet2[FactoryA]), DATEADD(values(Sheet2[Date]), -1, MONTH))

My real case is involving almost 100 of ProductName and 8 Factory. The date consists of monthly data over 4 years. In practice, I can still handle this with excel using many sheets and vlookup.

So, my question is:

  1. How can I solve this with Power BI? Can I use Measure to create the new table, for the sake of saving memories?
  2. Which course should I watch, so I can learn faster on this topic?
  3. @sam.mckay I want to post more screenshot for clarity but new user can only post 1 picture… :smile:

Thank you
Ivan

2

I think this is much easier than you are imagining right now. You don’t need a new column. Do this all in measures.

All you need to do is to create two aggegations.

FactoryA Total = SUM( FactoryA Column )

FactoryB Total = SUM( FactoryB Column )

Then create another to measures subtracting one from the other.

That’s it really, from what I can tell.

Make sure you use a date column from a date table, with the correct relationships.

Side note: you can just add more comments to your forum topics is you need to place more info in.

If you have many factories that you need to deal with versus the two that you are showing, you will want to ‘unpivot’ those columns in the query editor, so that the factory column headers all then go into one row.

You will only want to have one column with has the values in it.

Can you let me know how you go working through these suggestions. Thanks

3

Hi Sam,
It works now!

Actually what I meant is getting the monthly difference of each factory, so I didn’t use the SUM ones. Besides, it is a monthly data; only one row per month. But it enlightened me somehow :grin:

I did the ‘unpivot’ for the factory column header as you said, then using SUMX-FILTER, and CALCULATE-PREVIOUSMONTH to get the difference. Like this one:

Quantity A = SUMX( FILTER( Table, Table[Factory] = "Factory A" ), Table[Quantity] )

Monthly Difference A = [Quantity A] - CALCULATE( [Quantity A], PREVIOUSMONTH( Dates[Date] ) )

Thanks Sam

4

Great, nice work.