Solved: Powerquery - MTD

Anonymous · ‎12-12-2019

Trying to create custom column in Power Query for MTD

Column = Material, Date and Order Qty

I need MTD Order Qty against each transaction based on Material and Date

edhans · ‎12-12-2019

You are getting into date intelligence, and typically that is analysis and best done in DAX. Power Query for shaping, DAX for analysis. However, @ImkeF has done some solid work on cumulative/running totals in Power Query here, so you could look at that if this MUST be done in PQ. Unless you are dropping this to an Excel table vs the Power Pivot DAX model or the Power BI DAX model though, it really would be best done in DAX using the various date intelligence functions available in Power BI in measures.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

edhans · ‎12-12-2019

You are getting into date intelligence, and typically that is analysis and best done in DAX. Power Query for shaping, DAX for analysis. However, @ImkeF has done some solid work on cumulative/running totals in Power Query here, so you could look at that if this MUST be done in PQ. Unless you are dropping this to an Excel table vs the Power Pivot DAX model or the Power BI DAX model though, it really would be best done in DAX using the various date intelligence functions available in Power BI in measures.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting