Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.