topic calculate n days sales for locations with different start dates in Desktop
https://community.powerbi.com/t5/Desktop/calculate-n-days-sales-for-locations-with-different-start-dates/m-p/723883#M349369
<P>Hi All,</P><P> </P><P>Could you please help me out with this? My data is setup in the following way:</P><P> </P><P>a) Data table - contains order date, location name, customer id, product name, quantity, amount and order id</P><P>b) Calendar table - date table that is connected to the data table </P><P>c) Locationkey - contains locations and their respective start dates</P><P>for example: </P><TABLE><TBODY><TR><TD>Location </TD><TD>Start Date</TD></TR><TR><TD>Loc 1</TD><TD>9/1/2017</TD></TR><TR><TD>Loc 2</TD><TD>11/1/2017</TD></TR><TR><TD>Loc 3</TD><TD>2/2/2018</TD></TR><TR><TD>Loc 4</TD><TD>3/5/2018</TD></TR><TR><TD>Loc 5</TD><TD>7/6/2018</TD></TR><TR><TD>Loc 6</TD><TD>10/10/2018</TD></TR><TR><TD>Loc 7</TD><TD>12/22/2018</TD></TR><TR><TD>Loc 8</TD><TD>2/2/2019</TD></TR><TR><TD>Loc 9</TD><TD>7/4/2019</TD></TR><TR><TD>Loc 10</TD><TD>17/6/2019</TD></TR></TBODY></TABLE><P> </P><P>The expected result that would help me further would be:</P><P> </P><TABLE><TBODY><TR><TD>Location </TD><TD>1st week sales</TD><TD>2nd week sales</TD><TD>3rd week sales</TD><TD>4th week sales</TD><TD>5th week sales</TD><TD>6th week sales</TD><TD>7th week sales</TD><TD>8th week sales</TD><TD>9th week sales</TD><TD>10th week sales</TD><TD>11th week sales</TD><TD>nth week sales</TD></TR><TR><TD>Loc 1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>1st week sales would be sales for the first week after the launch date.</P><P> </P><P>Thanks </P><P> </P>Tue, 25 Jun 2019 15:17:50 GMTabhishekc12019-06-25T15:17:50Zcalculate n days sales for locations with different start dates
https://community.powerbi.com/t5/Desktop/calculate-n-days-sales-for-locations-with-different-start-dates/m-p/723883#M349369
<P>Hi All,</P><P> </P><P>Could you please help me out with this? My data is setup in the following way:</P><P> </P><P>a) Data table - contains order date, location name, customer id, product name, quantity, amount and order id</P><P>b) Calendar table - date table that is connected to the data table </P><P>c) Locationkey - contains locations and their respective start dates</P><P>for example: </P><TABLE><TBODY><TR><TD>Location </TD><TD>Start Date</TD></TR><TR><TD>Loc 1</TD><TD>9/1/2017</TD></TR><TR><TD>Loc 2</TD><TD>11/1/2017</TD></TR><TR><TD>Loc 3</TD><TD>2/2/2018</TD></TR><TR><TD>Loc 4</TD><TD>3/5/2018</TD></TR><TR><TD>Loc 5</TD><TD>7/6/2018</TD></TR><TR><TD>Loc 6</TD><TD>10/10/2018</TD></TR><TR><TD>Loc 7</TD><TD>12/22/2018</TD></TR><TR><TD>Loc 8</TD><TD>2/2/2019</TD></TR><TR><TD>Loc 9</TD><TD>7/4/2019</TD></TR><TR><TD>Loc 10</TD><TD>17/6/2019</TD></TR></TBODY></TABLE><P> </P><P>The expected result that would help me further would be:</P><P> </P><TABLE><TBODY><TR><TD>Location </TD><TD>1st week sales</TD><TD>2nd week sales</TD><TD>3rd week sales</TD><TD>4th week sales</TD><TD>5th week sales</TD><TD>6th week sales</TD><TD>7th week sales</TD><TD>8th week sales</TD><TD>9th week sales</TD><TD>10th week sales</TD><TD>11th week sales</TD><TD>nth week sales</TD></TR><TR><TD>Loc 1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Loc 10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>1st week sales would be sales for the first week after the launch date.</P><P> </P><P>Thanks </P><P> </P>Tue, 25 Jun 2019 15:17:50 GMThttps://community.powerbi.com/t5/Desktop/calculate-n-days-sales-for-locations-with-different-start-dates/m-p/723883#M349369abhishekc12019-06-25T15:17:50ZRe: calculate n days sales for locations with different start dates
https://community.powerbi.com/t5/Desktop/calculate-n-days-sales-for-locations-with-different-start-dates/m-p/724442#M349629
<P>Hi <LI-USER uid="143576"></LI-USER> ,</P>
<P> </P>
<P>Please check the following steps as below.</P>
<P> </P>
<P>1. To creat a date table and insert a calculated column in it.</P>
<PRE>DATE = CALENDARAUTO()</PRE>
<PRE>Column = WEEKNUM('DATE'[Date]) & "WEEK" & YEAR('DATE'[Date])</PRE>
<P>2. Create relationship between date table and <SPAN>Locationkey table.</SPAN></P>
<P> </P>
<P><SPAN>3. Then we can create a matrix as below.</SPAN></P>
<P> </P>
<P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 729px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/173227i62E48043E3B00826/image-size/large?v=v2&px=999" role="button" title="Capture.PNG" alt="Capture.PNG" /></span></SPAN></P>
<P> </P>
<P> </P>Wed, 26 Jun 2019 06:18:39 GMThttps://community.powerbi.com/t5/Desktop/calculate-n-days-sales-for-locations-with-different-start-dates/m-p/724442#M349629v-frfei-msft2019-06-26T06:18:39Z