Forum Discussion
DATEADD (PREVIOUS MONTH) + SUMMARIZE
Dear all.
I have an issue with a DAX formula (I work with Power Pivot, not with Power BI). Problem ussing at the same time DATEADD + SUMMARIZE.
To be easier, I created a simple example with dates (only 2), families, products, quantity and totals.
What that I would like to calculate:
- A Pivot Table where selected 1 month, will appear this month and the previos one. With "Families" subtotal (see below).
- Calculate the difference due to Price variation (Price month - Price previous month) * Quantity previous month
- Calculate the difference due to Quantity variation (Quantity month - Quantity previous month) * Price month.
I would like to obtein a Pivot Table with the same information as in this one:
Problem with subtotal and totals in the Var. Q and Var. P.
I tried with SUMMARIZE, but does not work with DATEADD...
Thank you!
Best Regards,
Javier
LEJ , Try add columns on top summzrize for such columns
example
measure =
Var _tab =
Addcolumns(SUMMARIZE(Table, Table[Product], Table[Month]) , "_1",[This Month], "_2", [Last Month] , "_3", [Last month Qty])
Return
Sumx(_tab, ([_1] -[_2])*[_3])
2 Replies
LEJ , Try add columns on top summzrize for such columns
example
measure =
Var _tab =
Addcolumns(SUMMARIZE(Table, Table[Product], Table[Month]) , "_1",[This Month], "_2", [Last Month] , "_3", [Last month Qty])
Return
Sumx(_tab, ([_1] -[_2])*[_3])
