Time Intelligence "The Hard Way" (TITHW) 07-28-2018 05:54 AM

[Greg_Deckler]

The Time intelligence features of Power BI are fantastic. But, let's be honest, they can also be a little frustating at times, have certain limitations and require data that actually has, well, dates, for one thing in addition to a separate Date/Calendar dimension table. And, let's further face the facts that we don't always have things like actual dates, sometimes we only have data like Year and Month or Year and Quarter. 

This Quick Measure presents some options for doing some common Time Intelligence measures "the hard way" so to speak. Essentially solving time intelligence issues with non-time intelligence DAX functions. While only a few measures are presented, generally around year and month, just about any time intelligence issue can be solved through the basic technique presented here.

Three measures are presented for Total Year to Date, Total Last Year to Date and % Change Year over Year. Here they are:

TITHW_TotalYTDHW = 
VAR __MaxYear = MAX('Years'[Year])
VAR __MaxMonth = MAX('Months'[MonthSort])
VAR __TmpTable = CALCULATETABLE('TheHardWay',ALL('Years'[Year]),All('Months'[Month]))
RETURN SUMX(FILTER(__TmpTable,[Year]=__MaxYear && [MonthSort] <= __MaxMonth),[Value])


TITHW_TotalLYTDHW = 
VAR __MaxYear = MAX('Years'[Year])
VAR __MaxMonth = MAX('Months'[MonthSort])
VAR __TmpTable = CALCULATETABLE('TheHardWay',ALL('Years'[Year]),All('Months'[Month]))
RETURN SUMX(FILTER(__TmpTable,[Year]=__MaxYear-1 && [MonthSort] <= __MaxMonth),[Value])


TITHW_%ChangeYoY = 
VAR __MaxYear = MAX('Years'[Year])
VAR __MaxMonth = MAX('Months'[MonthSort])
VAR __TmpTable = CALCULATETABLE('TheHardWay',ALL('Years'[Year]),All('Months'[Month]))
VAR __currentYear = SUMX(FILTER(__TmpTable,[Year]=__MaxYear && [MonthSort] <= __MaxMonth),[Value])
VAR __previousYear = SUMX(FILTER(__TmpTable,[Year]=__MaxYear - 1 && [MonthSort] <= __MaxMonth),[Value])
RETURN DIVIDE(__currentYear - __previousYear,__previousYear,0)

Now, this PBIX file uses separate Year and Month tables but those are not necessary, this was done to make comparing the time intelligence functions and the non-time intelligence way of doing things easier to compare. 

eyJrIjoiNjUzODY3NGYtNTY2NC00YTFjLTkxMjAtNDFjMWVmN2Q4OGQ1IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[st-dat]

Hello,

Thank you for your help, I will try the 2 suggestions on YTD and PYTD and provide feedback later. 

I will also appreciate your opinion on the 6 measures I posted for correction or guidance.

Many thanks for your kindness.

[PeteSmith6730]

Nice reference to YTD etc, but still not solved my issues.

[Greg_Deckler]

@PeteSmith6730 - And that problem is? Can you post a link to your original forums post?