Time Intelligence "The Hard Way" (TITHW)

Super User
1818 Views
Highlighted
Super User
Posts: 9,535
Registered: ‎07-11-2015

Time Intelligence "The Hard Way" (TITHW)

[ Edited ]

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. 

 

 

 

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Attachment
Frequent Visitor
Posts: 8
Registered: ‎06-25-2018

Re: Time Intelligence "The Hard Way" (TITHW)

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

Super User
Posts: 9,535
Registered: ‎07-11-2015

Re: Time Intelligence "The Hard Way" (TITHW)

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Member
Posts: 58
Registered: ‎09-27-2018

Re: Time Intelligence "The Hard Way" (TITHW)

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.

Member
Posts: 142
Registered: ‎06-26-2017

Re: Time Intelligence "The Hard Way" (TITHW)

Hi Greg, I still really value your formulas; however, I'm running into some challenges in comparing actuals to plan.

 

For example, let's say we're only one month into the new fiscal year, but we have the plan for the entire fiscal year loaded. In using this formula:


VAR __MaxYear = MAX('RA_Daily_Calendar'[Fiscal_Year])
VAR __MaxMonth = MAX('RA_Daily_Calendar'[Fiscal_Month])
VAR __TmpTable = CALCULATETABLE('RA_Daily_Calendar',ALL('RA_Daily_Calendar'[Fiscal_Year]),All('RA_Daily_Calendar'[Fiscal_Month]))
RETURN SUMX(FILTER(__TmpTable,[Fiscal_Year]=__MaxYear && [Fiscal_Month] <= __MaxMonth),
'Goals-Order$'[Total Order $ Goal])

 

It calculates the goal for the entire fiscal year, but I'm expecting/wanting only the goal for October.

 

To account for this, I modified the expression to look at the max order date in the actuals, like this (because I want to compare current YTD actuals to the corresponding YTD plan).

 

YTD Total Order $ Goal =
VAR __MaxYear = (LOOKUPVALUE('RA_Daily_Calendar'[Fiscal_Year], 'RA_Daily_Calendar'[Date], MAX('OrdersView'[Line_Creation_Date])))
VAR __MaxMonth = (LOOKUPVALUE('RA_Daily_Calendar'[Fiscal_Month], 'RA_Daily_Calendar'[Date], MAX('OrdersView'[Line_Creation_Date])))
VAR __TmpTable = CALCULATETABLE('RA_Daily_Calendar',ALL('RA_Daily_Calendar'[Fiscal_Year]),All('RA_Daily_Calendar'[Fiscal_Month]))
RETURN SUMX(FILTER(__TmpTable,[Fiscal_Year]=__MaxYear && [Fiscal_Month] <= __MaxMonth),
'Goals-Order$'[Total Order $ Goal])

 

In total, this formula structure seems to work properly. It is slick! However, I just discovered that when I try to drill into the actuals and goals down to finer levels of detail, like the product line, I run into issues. It appears that if there are no orders for this particular product yet this year, then it sees the latest date as being the last time there was an order for this product, which was last year, and does not sum the goals properly for this year.

 

In trying to figure out how to resolve this, I'm a bit perplexed. I thought the statement below was looking at the maximum date for the entire data set, but instead it appears to filter down to the lower levels - which I guess is understandable. 
VAR __MaxYear = (LOOKUPVALUE('RA_Daily_Calendar'[Fiscal_Year], 'RA_Daily_Calendar'[Date], MAX('OrdersView'[Line_Creation_Date])))

 

However, how can I modify this so it looks at the maximum year and month for the entire actual orders dataset? I was trying the ALL function, but it doesn't work with MAX. What am I missing? Or is there something else I could do to address this issue?

 

Thanks for any help you can provide. You've been great.

Shelley

Member
Posts: 142
Registered: ‎06-26-2017

Re: Time Intelligence "The Hard Way" (TITHW)

I think I finally got it! I finally figured out how to get ALL and MAX to work together -- CALCULATE(MAX('OrdersView'[Line_Creation_Date]), ALL('OrdersView'))) -- I have to check it some more, but I wanted to let you know I may have gotten it so you don't waste any time trying to help me. Thanks!

 

YTD Total Order $ Goal =
VAR __MaxYear = (LOOKUPVALUE('RA_Daily_Calendar'[Fiscal_Year], 'RA_Daily_Calendar'[Date], CALCULATE(MAX('OrdersView'[Line_Creation_Date]), ALL('OrdersView'))))
VAR __MaxMonth = (LOOKUPVALUE('RA_Daily_Calendar'[Fiscal_Month], 'RA_Daily_Calendar'[Date], CALCULATE(MAX('OrdersView'[Line_Creation_Date]), ALL('OrdersView'))))
VAR __TmpTable = CALCULATETABLE('RA_Daily_Calendar',ALL('RA_Daily_Calendar'[Fiscal_Year]),All('RA_Daily_Calendar'[Fiscal_Month]))
RETURN SUMX(FILTER(__TmpTable,[Fiscal_Year]=__MaxYear && [Fiscal_Month] <= __MaxMonth),
'Goals-Order$'[Total Order $ Goal])

Super User
Posts: 9,535
Registered: ‎07-11-2015

Re: Time Intelligence "The Hard Way" (TITHW)

You should also be able to do MAXX(ALL('Table'),[Column])


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Member
Posts: 142
Registered: ‎06-26-2017

Re: Time Intelligence "The Hard Way" (TITHW)

I can't get the MAXX function to work, and I just discovered that using my solution above for the maximum line creation date, doesn't work when filtering to the prior year. Arg. I can't believe how difficult it is to get Power BI to work properly for YTD actuals and plan and filter as expected. 

 

Regular Visitor
Posts: 22
Registered: ‎09-25-2018

Re: Time Intelligence "The Hard Way" (TITHW)

Hi Greg,

What would the formula look like to not just show the total year to date (7615 in your overview), but the liftime to date value, which can span across several years?

 

Capture.PNG

 

I have a report with a fiscal year and month filter where I want to see my total amount of outlets. This doesn't stop and start over when the year ends; in January it will need to continu counting the outlets, and I want to see the MoM result for January as well. If I would be using the FYTD calculation it will start over with counting as of January.

 

Would you have some advice for this, or is this perhaps a metric that you could add to your report?

Regards

Bas

Super User
Posts: 9,535
Registered: ‎07-11-2015

Re: Time Intelligence "The Hard Way" (TITHW)

Hmm, if I understand what you are asking for then I am thinking something along the lines of:

 

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

PC Load Letter. WTF does that mean?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!