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

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

View solution in original post

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.