Forum Discussion

masplin's avatar
masplin
Impactful Individual
4 years ago
Solved

Syntax for Tabular Editor to create Time intelligence functions

I have just started playing with using theTabular Editor to create standard time intelligence function e.g. WoW or MoM   Got it working great so can be dropped on any measure, but wondering if i ca...
Need Help
  • masplin's avatar
    masplin
    4 years ago

    I woudl still need to tell the formula to us Previous week as defined in the date table. 

     

    i came up with this rather clunky solution

    CALCULATE(SELECTEDMEASURE(),
          DATEADD(
                 CALCULATETABLE(
                               SUMMARIZE(Datetable,
                               Datetable[Date]),
                 Datetable[Current week]="Current"), 
         -7,DAY))

     to create a table of the current week dates to feed into DATEADD.  Not sure if any better way.  

     

    If I had Previous week defined in the date table could i just do this or refer to the WeeksFromNow=-1

     

    CALCULATE(SELECTEDMEASURE(),Datetable[Current week]="Previous")

     

    Good link to betterlooking dates table

mahoneypat's avatar
mahoneypat
Microsoft Employee
4 years ago

I would use a slightly different Date table, like the one described here - No Sort Date Tables! – Hoosier BI

It includes a WeeksFromNow column with integer values to make these kind of measures easier.  Current week is 0 and previous week is -1.

 

Pat

 

  • masplin's avatar
    masplin
    Impactful Individual
    4 years ago

    I woudl still need to tell the formula to us Previous week as defined in the date table. 

     

    i came up with this rather clunky solution

    CALCULATE(SELECTEDMEASURE(),
          DATEADD(
                 CALCULATETABLE(
                               SUMMARIZE(Datetable,
                               Datetable[Date]),
                 Datetable[Current week]="Current"), 
         -7,DAY))

     to create a table of the current week dates to feed into DATEADD.  Not sure if any better way.  

     

    If I had Previous week defined in the date table could i just do this or refer to the WeeksFromNow=-1

     

    CALCULATE(SELECTEDMEASURE(),Datetable[Current week]="Previous")

     

    Good link to betterlooking dates table