cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rstanleyraj
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

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 :

ProductDate
Bike8/22/2016
Clothing8/21/2016
Bike8/17/2016
Component7/30/2016
Bike7/29/2016
Accessory9/23/2015
Clothing8/22/2015
Bike7/17/2015
Bike4/30/2015
Accessory4/28/2015
1 ACCEPTED SOLUTION

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

 

View solution in original post

5 REPLIES 5
Eric_Zhang
Microsoft
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 () ) )
    )
)

Capture.PNG

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

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

 

View solution in original post

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

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors