Forum Discussion

Jmccoy's avatar
Jmccoy
Helper II
5 years ago
Solved

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]
)
Need Help
Tips and Tricks
  • parry2k's avatar
    parry2k
    5 years ago

    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's avatar
    AlB
    5 years ago

    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 

     

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      Jmccoy
      Helper II
      5 years ago

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

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      Jmccoy
      Helper II
      5 years ago

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