Time Intelligence Help

[Jmccoy]

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

ADDCOLUMNS(

GENERATE(

SELECTCOLUMNS({"Last 2 Months"},"Period",[Value]) ,

GENERATESERIES(

DATE(ThisYear , ThisMonth - 2 , 1) ,

Today

)

),"Axis Date",[Value]),

// Last 3 Months

ADDCOLUMNS(

GENERATE(

SELECTCOLUMNS({"Last 3 Months"},"Period",[Value]) ,

GENERATESERIES(

DATE(ThisYear , ThisMonth - 3 , 1) ,

Today

)

),"Axis Date",[Value]),

// Current Year

ADDCOLUMNS(

GENERATE(

SELECTCOLUMNS({"Current Year"},"Period",[Value]) ,

GENERATESERIES(

DATE(ThisYear , 1 , 1) ,

Today

)

),"Axis Date",[Value]),

// Prior Year

ADDCOLUMNS(

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

ADDCOLUMNS(

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

ADDCOLUMNS(

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]

)

[parry2k · Accepted solution]

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

instead of TODAY change it to

EOMONTH(Today,-1)

[AlB]

@Jmccoy 

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

            // Current week
            ADDCOLUMNS (
                GENERATE (
                    SELECTCOLUMNS ( { "Current week" }, "Period", [Value] ),
                    VAR weekStart_ = Today - (WEEKDAY(Today,2)-1)
                    RETURN GENERATESERIES ( weekStart_, weekStart_+6)
                ),
                "Axis Date", [Value]
            ),
            // Last week
            ADDCOLUMNS (
                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 

[Jmccoy]

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

[AlB · Accepted solution]

Hi @Jmccoy 

Something like this?

//Last Month 
ADDCOLUMNS (
    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 

[Jmccoy]

I am still having issues getting this week and last week. Please help!! 

[parry2k · Accepted solution]

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

instead of TODAY change it to

EOMONTH(Today,-1)

[Jmccoy]

Thank you very much!

[mahoneypat]

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