Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi ,
I am new to PowerBi. I have a sharepoint list with sales data for 2016 and 2015. I have to show the data in table format. I am able to connect to sharepoint using the sharepoint connector and retrive data. But i am having difficulty in calculating from 1/1/2015 to 22nd Aug and 1/1/2016 - Till date. Following is the sample input and output.
C
Thanks for your precious time and help in advance.
Output :
Product | 2016 (1/1/2016 - 22nd Aug) | 2015 (1/1/2015 - 22nd Aug) |
Bike | 3 | 2 |
Accessory | 1 | 1 |
Clothing | 1 | 1 |
Input :
Product | Date |
Bike | 8/22/2016 |
Clothing | 8/21/2016 |
Bike | 8/17/2016 |
Component | 7/30/2016 |
Bike | 7/29/2016 |
Accessory | 9/23/2015 |
Clothing | 8/22/2015 |
Bike | 7/17/2015 |
Bike | 4/30/2015 |
Accessory | 4/28/2015 |
Solved! Go to Solution.
Try touse a separate calendar table connecting your table using a 1:many relationship. And apply calendar[date] in the measures. Check more details in the attached PBIX.
2016 (1/1/2016 to Today) = CALCULATE ( COUNTROWS ( Table12 ), DATESBETWEEN ( 'calendar'[Date], "2016-01-01",TODAY() ) )
2015 (1/1/2015 - the same day of last year) = CALCULATE ( COUNTROWS ( Table12 ), DATESBETWEEN ( 'calendar'[Date], "2015-01-01", DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) ) )
Try two measures as
2016 (1/1/2016 to Today) = CALCULATE ( COUNTROWS ( Table12 ), DATESBETWEEN ( Table12[Date], "2016-01-01", TODAY () ) )
2015 (1/1/2015 - the same day of last year) = CALCULATE ( COUNTROWS ( Table12 ), DATESBETWEEN ( Table12[Date], "2015-01-01", DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) ) )
Thank you Eric.
I have implemented the solution you have mentioned. Getting the following error "A date column containing duplicates dates was specified in the call to function "DATESBETWEEN"". This is not supported.
Thanks,
Stanley
Try touse a separate calendar table connecting your table using a 1:many relationship. And apply calendar[date] in the measures. Check more details in the attached PBIX.
2016 (1/1/2016 to Today) = CALCULATE ( COUNTROWS ( Table12 ), DATESBETWEEN ( 'calendar'[Date], "2016-01-01",TODAY() ) )
2015 (1/1/2015 - the same day of last year) = CALCULATE ( COUNTROWS ( Table12 ), DATESBETWEEN ( 'calendar'[Date], "2015-01-01", DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) ) )
Hi Eric,
Thank you so much for the solution.
I have added the calendar table and used 1:many relationship and changed the measures as you have given, when I select the product I can see values but when I select the measures the table is empty.
My data is working with the Test.pbix file you have sent. I am missing something. Could you please help.
Following are my steps
1. Connect to Sharepoint list to get the data
2. Remove unwanted columns
3. Create calendar table using calendar = CALENDAR("2015-01-01","2016-12-31")
4. Map relationship with the date column [From list1(Date) to calendar(date)] .
5. Create measures.
6. Select the fields for the report.
Hi Eric,
I have changed two things, not sure which fixed the issue
1 .Added calendar table first and then queried Sharepoint to get the data
2. Added the following line in query Advanced Editor window
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Product", type text}, {"PurchasedDate", type date}})
Thanks for all your help.
Regards,
Stanley
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |