cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mbuhary
Helper I
Helper I

Sameperiodlastyear without dates in Fact Table

Hi All,

 

I have a fact table with only Year & month.
I have created a Date Table using these two columns via Calendar function using Min Year & month and Max Year & Month and marked it as a Date Table.

But When I try to create a measure for Previous Period Calculations the results appears without any filters for the whole available data.

Measure:

SalesValue = 
VAR SumValue=SUM(Daily_Sales[SalesValue])
VAR Result= CALCULATE(SumValue,SAMEPERIODLASTYEAR(d_Date[Date]))
Return Result

Any help is appreciated.Model.jpg

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@mbuhary , You can then create date from year and month 

example 

date = date([year], [month],1) // I can suggest more ways 

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@mbuhary , You can then create date from year and month 

example 

date = date([year], [month],1) // I can suggest more ways 

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thank you, I was thinking on the same lines, and it solved the Issue.

I removed the additional Table d_Year/Month and now directly created relationship of the fact table with date table. So now all Time Intelligence functions are working.

e.g. for same period last year,

 

 

 

mbuhary
Helper I
Helper I

Thank you for your reply in detail.

 

So do you mean that I will not be able to use any Time Intellignce functions using the Date Table ?

amitchandak
Super User IV
Super User IV

@mbuhary , With help of d_yearmonth you can get data of the previous year

 

new column in d_yearmonth 

Month Rank = RANKX(all('d_yearmonth '),'Date'[YrMonth],,ASC,Dense)

 

example measure

This Month = CALCULATE(sum('daily_sales'[Qty]), FILTER(ALL('d_yearmonth '),'d_yearmonth '[Month Rank]=max('d_yearmonth '[Month Rank]))


Last Month = CALCULATE(sum('daily_sales'[Qty]), FILTER(ALL('d_yearmonth '),'d_yearmonth '[Month Rank]=max('d_yearmonth '[Month Rank])-1))

 

Last year Month = CALCULATE(sum('daily_sales'[Qty]), FILTER(ALL('d_yearmonth '),'d_yearmonth '[Month Rank]=max('d_yearmonth '[Month Rank])-12))

 

 

//Only year vs Year, not a level below

This Year = CALCULATE(sum('daily_sales'[Qty]),filter(ALL('d_yearmonth '),'Date'[Year]=max('d_yearmonth '[Year])))
Last Year = CALCULATE(sum('daily_sales'[Qty]),filter(ALL('d_yearmonth '),'Date'[Year]=max('d_yearmonth '[Year])-1))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors