cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jmccoy
Helper II
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
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]
)
2 ACCEPTED SOLUTIONS
parry2k
Super User III
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)

 

 

 

 






Did I answer your question? Mark my post as a solution.

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





View solution in original post

AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

7 REPLIES 7
AlB
Super User III
Super User III

@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 

 

SU18_powerbi_badge

 

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

AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

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

parry2k
Super User III
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)

 

 

 

 






Did I answer your question? Mark my post as a solution.

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





View solution in original post

Thank you very much!

mahoneypat
Super User IV
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!

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


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors