Time Intelligence Calculations

[Rsanjuan]

Can someone please assist me in calculating week-to-date, month-to-date, and year-to-date, assuming I have the below formula:

Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

For the past 60 days, I used this formula and then used it as a visual filter

Last60Days = (IF(AND([Date] >= [Today]-60, [Date] <= [Today]),1,0))

I would like to do the same thing for WTD, MTD, and YTD.  However, I would like it to autoupdate, not just selecting the month or week.

Thanks!

[Greg_Deckler]

Month to date and year to date should be relatively easy:

DATESMTD

https://msdn.microsoft.com/en-us/library/ee634359.aspx

DATESYTD

https://msdn.microsoft.com/en-us/library/ee634221.aspx

Not sure about Week to Date, perhaps use WEEKDAY

https://msdn.microsoft.com/en-us/library/ee634550.aspx

in a formula like:

ThisWeek = (IF(AND([Date] >= [Today]-WEEKDAY([Today], [Date]) <= [Today]),1,0))

[Rsanjuan]

 @Greg_Deckler

First, thanks for your reply.  I tried the formula, and I got this error below:

Also,  I am very new to PowerBI, and not quite sure how to to the MTD and YTD calculations so that it returns a 1 or a 0.  Ideally, I wanted to create all these calculations as a measure, so I can put it all in one chart like below:

[Greg_Deckler · Accepted solution]

Apologies, was trying to hack your formula and I think I got a paren in the wrong place, try this one:

ThisWeek = (IF(AND([Date] >= [Today]-WEEKDAY([Today]), [Date] <= [Today]),1,0))

As for DATESMTD and DATESYTD, these return a column of date values and are generally used in conjunction with a date table. Potentially, you could use the same formula as above coupled with a MIN statement, so somthing like:

ThisWeek = (IF(AND([Date] >= [Date]>MIN(DATESYTD(DateTime[DateKey]), [Date] <= [Today]),1,0))