Forum Discussion
Display 12 weeks data
I've based this code on the below table structure. The way you worded 'Count of Customers' made me think there's a table somewhere that is being SUM'ed by every Friday to create a Measured Value. If this is the case, you may need to change the code to account for the raw data source. If you raw table looks like the first two columns below, you should be good to go.
1. (I'll explain why later.) Create a Custom Column in Query Editor called 12_Weeks that only has = "Customers".
2: Here's my code for the rolling 12 weeks (84 days). Since Dates are technnically just 5 digit numbers masked to look like dates in most databases, the straight math works fine.
12Wks_Calc = CALCULATE(SUM(Table1[CustomerCount]), FILTER(Table1, Table1[Date] >= (TODAY() - 84)))
3: Finally create a Matrix with Rows = '12_Weeks', Columns = 'Date', & Values = '12Wks_Calc'. Without the "Customers" text column, you won't see the header records you are expecting.
Hope this helps, FOrrest
Please post a sample of your raw data. Thank You
Above mentioned is the raw data.
Date Column
Count of Customer Column
Date CustomerCount
2017/10/13 25
2017/10/06 50
2017/09/29 50
2017/09/22 75
2017/09/15 100
2017/09/08 52
2017/09/01 150
2017/08/25 100
2017/08/18 125
2017/08/11 25
2017/08/04 300
2017/07/28 60
2017/07/21 25
2017/07/14 35
2017/07/07 100
2017/06/30 40
.......... .........
I need to display previous 12 weeks data i.e from 2017/07/28 to 2017/10/13.
Thanks.
I've based this code on the below table structure. The way you worded 'Count of Customers' made me think there's a table somewhere that is being SUM'ed by every Friday to create a Measured Value. If this is the case, you may need to change the code to account for the raw data source. If you raw table looks like the first two columns below, you should be good to go.
1. (I'll explain why later.) Create a Custom Column in Query Editor called 12_Weeks that only has = "Customers".
2: Here's my code for the rolling 12 weeks (84 days). Since Dates are technnically just 5 digit numbers masked to look like dates in most databases, the straight math works fine.
12Wks_Calc = CALCULATE(SUM(Table1[CustomerCount]), FILTER(Table1, Table1[Date] >= (TODAY() - 84)))
3: Finally create a Matrix with Rows = '12_Weeks', Columns = 'Date', & Values = '12Wks_Calc'. Without the "Customers" text column, you won't see the header records you are expecting.
Hope this helps, FOrrest