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
Anonymous
Not applicable

Show Today's Last Year Sale

Hello My Friends,

 

So I Have a DATASET like this:

 

ID DATE SALE

 

And This Dataset is fed a lot of times at the same day with new sales information.

 

So, What i wanna do is to Show The Sale that I Had 365 Days ago FROM TODAY inside a Card

I Also wanna show inside another Card the Sale that i HAD (365 -1) Day( Or D-1 Sale's)

 

So ,To show today's sale inside a card I Did:

 

TODAYSALE = CALCULATE ( SUM ( Table[ Sale] ) ; FILTER ( ALL ( Data ) ; Table [ Date ] = TODAY ( ) ))
 
I also was Able to do an Table that Show all days, the sales that were made that day and the sales that were made that day less one year:
 
LASTYEARSALE = CALCULATE( SUM( Table [ Sale ] ) ; DATEADD ( Table [ Date ] ; -1 ; Year))
 
Anyone has a hint?
1 ACCEPTED SOLUTION
Anonymous
Not applicable

The video was about how to build CORRECT data models. You should also learn how to correctly deal with dates. For this, please watch the following:

 

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

 

Your measures should be these (on condition the model is correct):

[Total Sales] = SUM( FactTable[Sales] )

[Today Sales] = 
	calculate(
		[Total Sales],
		'Calendar'[Date] = today()
	)

[Today Sales 1Yr Ago] =
var __today =
	calculatetable(
		firstdate( 'Calendar'[Date] ),
		'Calendar'[Date] = today()
	)
return
	calculate(
		[Total Sales],
		sameperiodlastyear(	
			__today
		)
	)

[Today Sales (1Yr - 1Day) Ago] =
var __todayPlusOneDay =
	calculatetable(
		firstdate( 'Calendar'[Date] ),
		'Calendar'[Date] = today() + 1
	)
return
	calculate(
		[Total Sales],
		sameperiodlastyear(	
			__todayPlusOneDay
		)
	)

Bear in mind that the above will not work if Calendar is not a Date table in the model.

 

Best

D

View solution in original post

4 REPLIES 4
Aburar_123
Resolver IV
Resolver IV

Hi,

 

You can use the below measure,

 

Actuals_LY-1 =
var last_year_date = MAXx('Date Dim',SAMEPERIODLASTYEAR('Date Dim'[Date]))
return CALCULATE([Actual],filter(ALL('Date Dim'),'Date Dim'[Date]=last_year_date-1))
 
Aburar_123_0-1637500270468.pngAburar_123_1-1637500306655.png

 

Anonymous
Not applicable

The video was about how to build CORRECT data models. You should also learn how to correctly deal with dates. For this, please watch the following:

 

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

 

Your measures should be these (on condition the model is correct):

[Total Sales] = SUM( FactTable[Sales] )

[Today Sales] = 
	calculate(
		[Total Sales],
		'Calendar'[Date] = today()
	)

[Today Sales 1Yr Ago] =
var __today =
	calculatetable(
		firstdate( 'Calendar'[Date] ),
		'Calendar'[Date] = today()
	)
return
	calculate(
		[Total Sales],
		sameperiodlastyear(	
			__today
		)
	)

[Today Sales (1Yr - 1Day) Ago] =
var __todayPlusOneDay =
	calculatetable(
		firstdate( 'Calendar'[Date] ),
		'Calendar'[Date] = today() + 1
	)
return
	calculate(
		[Total Sales],
		sameperiodlastyear(	
			__todayPlusOneDay
		)
	)

Bear in mind that the above will not work if Calendar is not a Date table in the model.

 

Best

D

Anonymous
Not applicable

Anonymous
Not applicable

Darlove,

 

thanks for the anwser...

 

I may be wrong but I did no see anything related in the video you sent me....

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.

Top Solution Authors