Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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

instead of TODAY change it to

EOMONTH(Today,-1)

 

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
Super User

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
Super User

@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
Super User

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

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

parry2k
Super User
Super User

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

instead of TODAY change it to

EOMONTH(Today,-1)

 

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Thank you very much!

mahoneypat
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.