https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-measure-for-rolling-one-year-calculation/m-p/1037391#M13837 <P>Hi all,</P><P>I need to create a measure for the following simplified scenario.&nbsp;</P><P>In my data set there is a date column and a value calumn. There is one record per day ( exccluding weekends and hilodays ) in this dataset.</P><P>In my visualizaation I have to skip first 252 days (first year )&nbsp;, and for any given date after that divide the value for that date by the value for the day 252 prior to that date.</P><P>example&nbsp;</P><P>Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Value</P><P>5/5/2016 ( start date)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 101</P><P>5/6/2016&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 99</P><P>...</P><P>5/5/2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 105</P><P>5/6/2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 103</P><P>....</P><P>------------------------------------------------------------------------------------------------------------------</P><P>My visualtion should display the following&nbsp;.&nbsp;&nbsp; Please note I don't wnat to show any records prior to 5/5/2017 in the visualization because there is no data for dates prior&nbsp;to&nbsp;&nbsp; 5/5/2016 in the dataset&nbsp;&nbsp;&nbsp;&nbsp; (Date - Year ago)</P><P>5/5/2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.039&nbsp;&nbsp;&nbsp; (calculation is : 105/101)</P><P>5/6/2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;1.040&nbsp;&nbsp;&nbsp;(103/99)</P><P>........&nbsp;&nbsp;</P><P>I hopw I was able to exxplain the requirment clearly. Thank you for you help.</P> Mon, 20 Apr 2020 18:58:49 GMT Anonymous 2020-04-20T18:58:49Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-measure-for-rolling-one-year-calculation/m-p/1037391#M13837 <P>Hi all,</P><P>I need to create a measure for the following simplified scenario.&nbsp;</P><P>In my data set there is a date column and a value calumn. There is one record per day ( exccluding weekends and hilodays ) in this dataset.</P><P>In my visualizaation I have to skip first 252 days (first year )&nbsp;, and for any given date after that divide the value for that date by the value for the day 252 prior to that date.</P><P>example&nbsp;</P><P>Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Value</P><P>5/5/2016 ( start date)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 101</P><P>5/6/2016&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 99</P><P>...</P><P>5/5/2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 105</P><P>5/6/2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 103</P><P>....</P><P>------------------------------------------------------------------------------------------------------------------</P><P>My visualtion should display the following&nbsp;.&nbsp;&nbsp; Please note I don't wnat to show any records prior to 5/5/2017 in the visualization because there is no data for dates prior&nbsp;to&nbsp;&nbsp; 5/5/2016 in the dataset&nbsp;&nbsp;&nbsp;&nbsp; (Date - Year ago)</P><P>5/5/2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.039&nbsp;&nbsp;&nbsp; (calculation is : 105/101)</P><P>5/6/2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;1.040&nbsp;&nbsp;&nbsp;(103/99)</P><P>........&nbsp;&nbsp;</P><P>I hopw I was able to exxplain the requirment clearly. Thank you for you help.</P> Mon, 20 Apr 2020 18:58:49 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-measure-for-rolling-one-year-calculation/m-p/1037391#M13837 Anonymous 2020-04-20T18:58:49Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-measure-for-rolling-one-year-calculation/m-p/1037734#M13852 <P>See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.<BR /><BR /><A href="https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008" target="_blank">https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008</A></P> Mon, 20 Apr 2020 22:03:24 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-measure-for-rolling-one-year-calculation/m-p/1037734#M13852 Greg_Deckler 2020-04-20T22:03:24Z