Forum Discussion

mbuhary's avatar
mbuhary
Icon for Helper I rankHelper I
5 years ago
Solved

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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"))

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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"))

 

 

mbuhary's avatar
mbuhary
Icon for Helper I rankHelper I
5 years ago

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,