Time Intelligence "The Hard Way" (TITHW) 06-06-2018 12:53 PM - last edited 06-06-2018 14:29 PM

[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

[daniel_baciu]

Using your data model, i tried to create a line chart/matrix where to show last year YTD for every period, and current year YTD until month of June for example. 

The existing measures cannot provide this, so may i kindly ask you to provide a solution to this? 

Result is shown below and pbix is here.

[learning_dax]

Hi @Greg_Deckler,

Diving into all of your posts thanks for the time & effort. I'm just having a hard time implementing your "Hard Way" formulas, and I cannot seem to get Power BI's normal TI to work. Can you explain what some of the entries mean in your "Hard Way" formulas like [Year] [Month Sort] and why in CALCULATETABLE calculation it is ALL('Months'[Month]) instead of in a previous variable 'Months'[MonthSort]? I'd like to just understand these formulas a bit better so that I can implement them the hard way. 

Thank you

[Greg_Deckler]

@learning_dax Sure, those are columns. Download the attached PBIX file just below the signature on the original post to understand what is going on. The ALL() is removing the filter on Month since you need to break out of filter context of the visual to include previous months for example.

[abristow]

@Greg_Deckler question for you on this: What adjustments would I need to make so that I can dynamically visualize weekly data as a trend on a bar chart vs summing the desired # of weeks?

Context: 

One of my data sources pushes updates each week with a number of pre-aggrigated time period sums. They appear in a slicer like this:

I also recieve sums for individual weeks and visualize them as a trend like this:

I am looking for a way to have the weekly trend visual update based on the number of weeks referenced in my slicer selection. 

I created this measure using your THW method but it's giving me a single sum of 1, 4, 13 weeks, etc.:

Weekly Trend_Test = 
VAR PeriodDescriptionSelected = SELECTEDVALUE ( 'date'[Period Description] )
VAR NumOfCharacters = LEN ( PeriodDescriptionSelected ) 
VAR WeeksToDisplay = 
IF (
    NumOfCharacters = 27, // <-- Latest # wks
    MID ( PeriodDescriptionSelected, 8, 1 ),
    IF (
        NumOfCharacters = 28, // <-- Latest ## wks
        MID ( PeriodDescriptionSelected, 8, 2 ),
        IF (
            NumOfCharacters = 20, // <-- YTD # w/e
            MID ( PeriodDescriptionSelected, 7, 1 ),
            IF (
                NumOfCharacters = 21, // <-- YTD ## w/e
                MID ( PeriodDescriptionSelected, 7, 2 ),
                IF (
                    NumOfCharacters = 26, // <-- Latest 1 wk
                    MID ( PeriodDescriptionSelected, 8, 1 )
                )
            )
        )
    )
)
VAR MaxPeriodEndingDate = MAX ( 'date'[Period Ending Date] )
VAR FirstPeriodEndingDate = MaxPeriodEndingDate - ( WeeksToDisplay * 7 )
VAR _TmpTable = 
CALCULATETABLE (
    'fact scan data',
    ALL ( 'date'[Period Ending Date] )
)

RETURN
SUMX (
    FILTER (
        _TmpTable,
        [Period Ending Date] >= FirstPeriodEndingDate
        && [Period Ending Date] <= MaxPeriodEndingDate
    ),
    [VALUE]
)

Any guidance you can provide would be greatly appreciated! 

[Greg_Deckler]

@abristow Hmm, without seeing sample data, perhaps try this (below). Does your fact_scan_data table have a Period Ending Date column?

Weekly Trend_Test = 
VAR PeriodDescriptionSelected = SELECTEDVALUE ( 'date'[Period Description] )
VAR NumOfCharacters = LEN ( PeriodDescriptionSelected ) 
VAR WeeksToDisplay = 
IF (
    NumOfCharacters = 27, // <-- Latest # wks
    MID ( PeriodDescriptionSelected, 8, 1 ),
    IF (
        NumOfCharacters = 28, // <-- Latest ## wks
        MID ( PeriodDescriptionSelected, 8, 2 ),
        IF (
            NumOfCharacters = 20, // <-- YTD # w/e
            MID ( PeriodDescriptionSelected, 7, 1 ),
            IF (
                NumOfCharacters = 21, // <-- YTD ## w/e
                MID ( PeriodDescriptionSelected, 7, 2 ),
                IF (
                    NumOfCharacters = 26, // <-- Latest 1 wk
                    MID ( PeriodDescriptionSelected, 8, 1 )
                )
            )
        )
    )
)
VAR MaxPeriodEndingDate = MAX ( 'date'[Period Ending Date] )
VAR FirstPeriodEndingDate = MaxPeriodEndingDate - ( WeeksToDisplay * 7 )
RETURN
SUMX (
    FILTER (
        ALL('fact scan data'),
        [Period Ending Date] >= FirstPeriodEndingDate
        && [Period Ending Date] <= MaxPeriodEndingDate
    ),
    [VALUE]
)

[Anonymous]

Great. Yipeee, That solved my issue. Thanks....

[Anonymous]

Can you please help on this. This is my MTD data. My requirement is to create YTD for the same with opening. Please go through my PBIX file

[charleshale]

How would you add a measure to show the $ amount of lost customers for any customer?

[Anonymous]

Hi Greg,

Thank you for your repsonse and I absoletly like it. However I need a dynamic month over month average with variance and percentage. I'm not sure how to apply your methodology to this.

Tom

[MojoGene]

Greg:

I'll give it a try.

[Shelley]

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

[Shelley]

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

[Greg_Deckler]

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

[Shelley]

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. 

[Anonymous]

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?

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

[Greg_Deckler]

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?

[Anonymous]

Haha I had to look that quote up, but you're the first one who spotted the avatar pic 🙂

I tried your suggestion but couldn't get it to fully work, and then landed on this calculation which did the trick for me:

CALCULATE(
SUM(calculation),
DATESBETWEEN(_Date[Date], DATE(1901,1,1), LASTDATE(_Date[Date]) )
)

Thanks for getting me there!
Bas

[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?