cancel
Showing results for
Did you mean:
Frequent Visitor

## 2016 and 2015 sales data till current date for both years

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

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
1 ACCEPTED SOLUTION
Microsoft

@rstanleyraj

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 () ) )
)
)```

5 REPLIES 5
Microsoft

@rstanleyraj

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 () ) )
)
)```

Frequent Visitor

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

Microsoft

@rstanleyraj

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 () ) )
)
)```

Frequent Visitor

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.

Frequent Visitor

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

#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Product", type text}, {"PurchasedDate", type date}})

Regards,
Stanley

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.