Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
masplin
Impactful Individual
Impactful Individual

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

 

Capture.JPG

 

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

1 ACCEPTED SOLUTION
masplin
Impactful Individual
Impactful Individual

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

View solution in original post

2 REPLIES 2
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


masplin
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.