cancel
Showing results for
Did you mean:
Helper II

## Time Intelligence Help

I am refering to the article below to calculate a table to be used for time intelligence.
Phil Seamark on DAX

I have been able to recreate a few of the options I needed following the same pattern. The ones I am having trouble with are: Last Month, Last Week, This week. When Last month for example is select I would like for it to only show the months in last month. Currently the formula below will calculate all of last month and up to the current date.

Time Intelligence =
VAR Today = Today()
VAR ThisYear = YEAR(Today)
VAR ThisMonth = MONTH(Today)
VAR ThisDay = DAY(Today)
RETURN
SELECTCOLUMNS(
UNION
(
// Last 2 Months
GENERATE(
SELECTCOLUMNS({"Last 2 Months"},"Period",[Value]) ,
GENERATESERIES(
DATE(ThisYear , ThisMonth - 2 , 1) ,
Today
)
),"Axis Date",[Value]),

// Last 3 Months
GENERATE(
SELECTCOLUMNS({"Last 3 Months"},"Period",[Value]) ,
GENERATESERIES(
DATE(ThisYear , ThisMonth - 3 , 1) ,
Today
)
),"Axis Date",[Value]),

// Current Year
GENERATE(
SELECTCOLUMNS({"Current Year"},"Period",[Value]) ,
GENERATESERIES(
DATE(ThisYear , 1 , 1) ,
Today
)
),"Axis Date",[Value]),

// Prior Year
GENERATE(
SELECTCOLUMNS({"Prior Year"},"Period",[Value]) ,
GENERATESERIES(
DATE(ThisYear-1 , 1 , 1) ,
DATE(ThisYear,ThisMonth-12,ThisDay)
)
),
"Axis Date",DATE(YEAR([Value]),MONTH([Value])+12,DAY([Value])
)
),

// Last Month
GENERATE(
SELECTCOLUMNS({"Prior Year"},"Period",[Value]) ,
GENERATESERIES(
DATE(ThisYear , ThisMonth -1 , 1) ,
DATE(ThisYear,ThisMonth-1,ThisDay)
)
),
"Axis Date",DATE(YEAR([Value]),MONTH([Value])+12,DAY([Value])
)
),

// Last 28 Days
GENERATE(
SELECTCOLUMNS({"Last 28 Days"},"Period",[Value]) ,
GENERATESERIES(
DATE(ThisYear , ThisMonth , ThisDay-28) ,
Today-1
)
),
"Axis Date",[Value]
)
,

// Totals YTD

GENERATE(
SELECTCOLUMNS({"Totals YTD"},"Period",[Value]) ,
VAR BaseTable =
SELECTCOLUMNS(
GENERATESERIES(
DATE(ThisYear , 1 , 1) ,
Today
),"D1",[Value]
)
RETURN
SELECTCOLUMNS(
GENERATE(
BaseTable ,
FILTER(
SELECTCOLUMNS(BaseTable,"D2",[D1]) ,[D2]<=EARLIER([D1]))
)
,"Date",[D2]
,"Axis Date",[D1]
)
)

) ,
"Date" , [Value] ,
"Period" , [Period] ,
"Axis Date" , [Axis Date]
)
2 ACCEPTED SOLUTIONS
Super User III

@Jmccoy make this change in your DAX expression for last 2 and 3 months logic

``````instead of TODAY change it to

EOMONTH(Today,-1)``````

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User III

Hi @Jmccoy

Something like this?

``````//Last Month
GENERATE (
SELECTCOLUMNS ( { "Last Month" }, "Period", [Value] ),
GENERATESERIES (
EDATE ( DATE ( ThisYear, ThisMonth, 1 ), -1 ),
DATE ( ThisYear, ThisMonth, 1 ) - 1
)
),
"Axis Date", [Value]
)
``````

and similar for the others

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

7 REPLIES 7
Super User III

You should show what the exact expected result is. Try this

``````            // Current week
GENERATE (
SELECTCOLUMNS ( { "Current week" }, "Period", [Value] ),
VAR weekStart_ = Today - (WEEKDAY(Today,2)-1)
RETURN GENERATESERIES ( weekStart_, weekStart_+6)
),
"Axis Date", [Value]
),
// Last week
GENERATE (
SELECTCOLUMNS ( { "Last week" }, "Period", [Value] ),
VAR weekStart_ = Today-7 - (WEEKDAY(Today-7,2)-1)
RETURN GENERATESERIES ( weekStart_, weekStart_+6)
),
"Axis Date", [Value]
)``````

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Helper II

Thank you so much, this is working exactly how I would like it to.

Super User III

Hi @Jmccoy

Something like this?

``````//Last Month
GENERATE (
SELECTCOLUMNS ( { "Last Month" }, "Period", [Value] ),
GENERATESERIES (
EDATE ( DATE ( ThisYear, ThisMonth, 1 ), -1 ),
DATE ( ThisYear, ThisMonth, 1 ) - 1
)
),
"Axis Date", [Value]
)
``````

and similar for the others

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Helper II

Super User III

@Jmccoy make this change in your DAX expression for last 2 and 3 months logic

``````instead of TODAY change it to

EOMONTH(Today,-1)``````

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper II

Thank you very much!

Super User IV

Please see if this video helps.  It's a different approach but may create the columns for relative day, week, month, etc. you need for your calculations.

Power BI Tales From The Front - Day/Week/Month/Quarter/Year Indices - YouTube

Regards,

Pat

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

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks