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
thatjoey20
Helper I
Helper I

Issues with last year revenue

Hello, 

I created a Calendar table;

thatjoey20_0-1626102390076.png

 

And relationed with the date column of my order table;

thatjoey20_1-1626102432200.png

 

Then I created a measure to sum the revenue of the last year

 

10M - Revenue LY = CALCULATE([01M -Total Revenue], SAMEPERIODLASTYEAR('Calendar'[Date Emissão]))
 
But when compared the values in a table graphic, something strange happens;

thatjoey20_2-1626102758784.png

 

It's just ignoring more than 50% of the value, its like it can't find a date to it, but if we check the date columns of both tables;

thatjoey20_3-1626102825995.png

 

Calendar has dates from 01/01/19 til 31/12/2021

And if we check date column from order table we see that;

thatjoey20_4-1626102923820.png

 

It has date from 04/01/2019 until 12/07/2021 (today)

Why this is happenig?

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@thatjoey20 , check if the date of the table joining to join to the calendar is having a timestamp.

You need to make date as date time and choose a format with time to check.

 

If it has time create date without time and join with calendar

 

This should work

10M - Revenue LY = CALCULATE([01M -Total Revenue], SAMEPERIODLASTYEAR('Calendar'[Date Emissão]))

10M - Revenue LY = CALCULATE([01M -Total Revenue], dateadd('Calendar'[Date Emissão],-1,year))

 

also, make sure the Calendar is marked as a date table 

Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@thatjoey20 , check if the date of the table joining to join to the calendar is having a timestamp.

You need to make date as date time and choose a format with time to check.

 

If it has time create date without time and join with calendar

 

This should work

10M - Revenue LY = CALCULATE([01M -Total Revenue], SAMEPERIODLASTYEAR('Calendar'[Date Emissão]))

10M - Revenue LY = CALCULATE([01M -Total Revenue], dateadd('Calendar'[Date Emissão],-1,year))

 

also, make sure the Calendar is marked as a date table 

Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw

Alright, but how do I create date column without timestamp?  I copy the former date column?

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.