Syntax for Tabular Editor to create Time intelligence functions

[masplin]

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 can combine Wow and MoM in the same visual?

To get the current Week data I started with defining experession as Current week=SELECTEDMEASURE() and then applying a filter on the visual of the current week.   Similarly in a seperate visual I have have Current Month=SELECTEDMEASURE() and apply a filter for current month.

However I need the visual to show WoW and MoM, but you cant filter a single visual by both current week and current month

I can force the current Week to always be current week witohut any visual filter by using (I have a column in my date table working out the current dates)

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

 and current Month

CALCULATE(SELECTEDMEASURE(),Datetable[Current Month]="Current")

This again works fine and I can put both in one visual with no filter as you can see below. Right hand has no filters on the visual

So far so good.  Then I need to calcuate the Previous week and previous month.  Originally I had this which works fine if you use a visual filter.  Now I've removed the date filter on thevisual I need ot somehow tel lthis formula its starting with a set of dates defined by the "Current week" filter on the dates table.  I have tried all sorts of syntax and none of them work?

CALCULATE(SELECTEDMEASURE(),    DATEADD('DateTable'[Date],-7,DAY))

Any idea how to make DATEADD shoft the dates from a predefined set of dates inside the formula?

Any help apprciated

Mike

[mahoneypat]

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 · Accepted solution]

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