cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors