topic Matrix with dynamic columns of the last 3 years in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Matrix-with-dynamic-columns-of-the-last-3-years/m-p/858844#M6905 <P>Hi</P><P>&nbsp;</P><P>I'm trying to create a matrix table that shows the sales at two specific dates each year for the last 3 years.</P><P>The specific dates are february 1st and july 1st.</P><P>As of today, the table should have 7 date columns: july 1st 2016, february 1st 2017....july 1st 2019.&nbsp;</P><P>I want the table to show every sales date in between the newest sales date and 3 years back. As of today that is july 1st 2016 to july 1st 2019.</P><P>When we get to february 1st 2020 the measure should dynamically change the columns so the newest sales date is february 1st 2020 and the oldest is february 1st 2017.</P><P>Something like this:</P><P>&nbsp;</P><P>What the table should show today:</P><TABLE border="1"><TBODY><TR><TD>&nbsp;</TD><TD>20160701</TD><TD>20170201</TD><TD>20170701</TD><TD>20180201</TD><TD>20180701</TD><TD>20190201</TD><TD>20190701</TD></TR><TR><TD>sales</TD><TD>1000</TD><TD>1000</TD><TD>2000</TD><TD>2000</TD><TD>1000</TD><TD>1500</TD><TD>1200</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>What the table should show when we reach february 1st 2020:</P><TABLE border="1"><TBODY><TR><TD>&nbsp;</TD><TD>20170201</TD><TD>20170701</TD><TD>20180201</TD><TD>20180701</TD><TD>20190201</TD><TD>20190701</TD><TD>20200201</TD></TR><TR><TD>sales</TD><TD>1000</TD><TD>2000</TD><TD>2000</TD><TD>1000</TD><TD>1500</TD><TD>1200</TD><TD>2000</TD></TR></TBODY></TABLE><P>Thanks</P> Wed, 27 Nov 2019 18:44:57 GMT nimp_kp 2019-11-27T18:44:57Z Matrix with dynamic columns of the last 3 years https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Matrix-with-dynamic-columns-of-the-last-3-years/m-p/858844#M6905 <P>Hi</P><P>&nbsp;</P><P>I'm trying to create a matrix table that shows the sales at two specific dates each year for the last 3 years.</P><P>The specific dates are february 1st and july 1st.</P><P>As of today, the table should have 7 date columns: july 1st 2016, february 1st 2017....july 1st 2019.&nbsp;</P><P>I want the table to show every sales date in between the newest sales date and 3 years back. As of today that is july 1st 2016 to july 1st 2019.</P><P>When we get to february 1st 2020 the measure should dynamically change the columns so the newest sales date is february 1st 2020 and the oldest is february 1st 2017.</P><P>Something like this:</P><P>&nbsp;</P><P>What the table should show today:</P><TABLE border="1"><TBODY><TR><TD>&nbsp;</TD><TD>20160701</TD><TD>20170201</TD><TD>20170701</TD><TD>20180201</TD><TD>20180701</TD><TD>20190201</TD><TD>20190701</TD></TR><TR><TD>sales</TD><TD>1000</TD><TD>1000</TD><TD>2000</TD><TD>2000</TD><TD>1000</TD><TD>1500</TD><TD>1200</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>What the table should show when we reach february 1st 2020:</P><TABLE border="1"><TBODY><TR><TD>&nbsp;</TD><TD>20170201</TD><TD>20170701</TD><TD>20180201</TD><TD>20180701</TD><TD>20190201</TD><TD>20190701</TD><TD>20200201</TD></TR><TR><TD>sales</TD><TD>1000</TD><TD>2000</TD><TD>2000</TD><TD>1000</TD><TD>1500</TD><TD>1200</TD><TD>2000</TD></TR></TBODY></TABLE><P>Thanks</P> Wed, 27 Nov 2019 18:44:57 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Matrix-with-dynamic-columns-of-the-last-3-years/m-p/858844#M6905 nimp_kp 2019-11-27T18:44:57Z