Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |