Forum Discussion

bourne2000's avatar
bourne2000
Icon for Helper V rankHelper V
4 years ago

Getting empty when I connect with my calendar table

Hi

 

I have a table called Journal  which consist od Journal ID, Date, Journal, Editor. I am creating visual for number of journals vs month which I can able to do. Now I want to do Year on Year % so I have created a date table and connected the date into Date Journal.

 

 

It's many to one relationship

 

I am creating a table now, by dragging year from the date table vs Count of Journal from Journal table, And getting below result

 

 

Seems, my date table is not recognize. Can anyone advise what is the issue here? I am trying look into this for a long time, not able to do so. Please advise

 

Need Help
Show and Tell

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    bourne2000 , You should date table's date with date in Journal table , then use time intelligence. If needed create date from year and month

     

    example

     

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

     

    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA