Forum Discussion

EpicTriffid's avatar
EpicTriffid
Helper IV
6 years ago

The Secret to Time Intelligence?

Morning all,

 

I don't have much hair left, but my understanding, or misunderstanding, of Time Intelligence is rapidly reducing even those few wisps to dust. My attempts at getting Time Intelligence to work are hit and miss at best, and throwing things at the wall to see what sticks at worst. Is there some fundamental secret I am not getting here? I would assume it's probably to do with the Evaluation Contexts, as I don't seem to have a full grasp of the theory yet, but I just really do not know. 

 

Here is my latest dilemma:

 

I deal with academic years (19/20, 20/21, etc.) in my data. I have a Date table linked to this with actual dates related to each academic year:

 

 

It is this table I am using to guide my time intelligence. I used the same thing in a previous report that worked with Time Intelligence. Using this I can have a CurrentYear measure that works perfectly fine:

 

 

CurrentYear = CALCULATE(
    SUM(Query2[Total Student Count]), 
    YEAR('Date'[Year Date]) = YEAR(TODAY()))

 

 

I can add this into a table as below:

Works great. Exactly what I want. 

 

But if I use SAMEPERIODLASTYEAR, or any derivitives of it, for my LastYear measure, it doesn't work:

 

 

LastYear = 
            CALCULATE(
                SUM(
                    Query2[Total Student Count]), 
                    SAMEPERIODLASTYEAR('Date'[Year Date].[Date])
)

 

I've removed the .[Date] from it, and even added in an additional ALL('Date') filter at the end as was suggested in some blogs. My aim is that I can then have a difference between years by subtracting one from the other but if I do that I get:

Is this how it is supposed to work? Am I doing something wrong? Is my Date Table wrong? Or is my understanding flawed? 

 

I can get around this issue of a LastYear Measure by doing something like:

 

 

LastYear = CALCULATE(
    SUM(Query2[Total Student Count]), 
    YEAR('Date'[Year Date]) = YEAR(TODAY()) - 1 )

 

 

But that seems to bypass the Time Intelligence functions as I understand them. 

 

Ultimately, I can make it work, but feel I'm missing out on functions within PBI that would really help, as well as not being able to use PBI to the best of my ability.  

 

Any help would be appreciated. I hope that any answers would help others in the future who might be experiencing this.

Need Help
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

     

     

    EpicTriffid 

    For date does not use .date unless you have a timestamp. I am not what is year Date. But you should date from date calendar marked at the date. Sampleperiodlast year should work. Prefer giving the current year measure in that with the only date

     

    For Time intelligence prefer a date calendar table marked as the date.  Use Time intelligence functions like dates* , total* , Previous* , sampleperiodlastyear etc

     

    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 MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

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

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year + 3 week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day))
55 weeks behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],365+12,Day))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
Cumm Days = CALCULATE(distinctcount(date[date]),filter(date,date[date] <=max(Sales[Sales Date])))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    I think it is because you should have continuous date in [Year Date] column.

    And could you please share your sample data and relationships(and link columns) between them as screenshots here?

     

    Best Regards,

    Giotto