Time Intelligence (Beginner Help Please!!)

[learning_dax]

Hi all, 

Loving Power BI & Dax. New user here picking things up quickly but struggling in some areas compared to others. However, time-intelligence functions (MTD, PREVIOUSMONTH, DateAdd) have not seemed to work for me. I am doing relatively simple calculations, calculating COUNT of visits by a date column in Excel "Visit Date." Thus, I can provide insights on customer visits by using their customer counts, within certain time ranges, YoY, MoM, PreviousMonth, etc.

Knowing its best to use a Date Table, I use RADACAD's script: https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-.... I define it in PowerBI as a date table, and in my time intelligence functions I refer back to the 'Date' [Date] column as it is my Datekey. 

However, no matter what function I use PREVIOUSMONTH, MTD, DateAdd (-1,Month), & others, I either receive blanks, "Something's wrong with one or more fields", or simply it gives me the default totals and is not doing any time filtering at all. Any suggestions, as I am struggling to pick this aspect of Dax/Power BI up. 

Here are some of the calculations I tried:

=Calculate(Count(‘Customer Visits’ [Visit Date]),PREVIOUSMONTH(‘Date’ [Date]))

=Calculate(Count(‘Customer Visits’ [Visit Date]),Dateadd(‘Date’ [Date],-1,Month)))

=TotalMTD(Count('Customer Visit' [Visit Date]),'Date' [Date])

Currently, the only way I've been able to circumvent this is by using Month Offset filters, but that limits my ability to compare values within the same viz (bar charts, tables, etc). Any suggestions or help as to why this is happening?

[ValtteriN]

Hi,

Your DAX seems to be fine so the problem is likely caused by either your relationship between calendar and your fact table or the actual data. The only DAX that I would change is instead of TOTALMTD I would use like this:

RT_previousMonth = CALCULATE([example],DATESMTD('Calendar'[Date]))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[learning_dax]

Hi, 

Went ahead and tried this and it returned blank. Also, my DATEADD functions don't do any filtering, no matter if i set the interval to -1, -2, it simply returns the entire counts.

[ValtteriN]

How is the relationship between your calendar table and fact table?

[learning_dax]

There's only two tables, and the relationship is one to many, the many side being my fact table. It is joined via my [Date] key on my Date table and [Customer Visit Date] on my fact table. 

[v-rzhou-msft · Accepted solution]

Hi @learning_dax ,

I think your dax code is correct. The issue should be caused by your date table. How did you get your Date table, by Enter Data or calculated table by calendar/calendarauto dax function? To succeed to use time intelligence, please make sure your date table has continuous dates in it. I suggest you to use calendar/calendarauto dax function to create a date table with continuous dates. 

For reference: Create date tables in Power BI Desktop

Below screenshot is my test result. Top table show the correct results based on continuous date table. Below one show incorrect result based on uncontinuous date table.

You may download my sample to learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.