Sum of visitors within different time range, including week to date, month to date, year to date etc

[Lu]

Hi,

I am looking for the solution to calculate the sum of different types of visitors in the different time range. I  used to do this on SSRS, and we are planning to move the report to Power BI.

In SSRS, we set the time range parameters as:

@StartDate_CurrWk =DATEADD("d", 1 - DATEPART(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday),Dateadd(DateInterval.WeekOfYear,-1,Today()))

@EndDate_CurrWk =DateAdd("d",6,Parameters!StartDate_CurrWk.Value)

@StartDate_PrevWk =DateAdd("d", -13,Parameters!EndDate_CurrWk.Value)

@EndDate_PrevWk=DateAdd("d", -7,Parameters!EndDate_CurrWk.Value)

@StartDate_SWLY=DateAdd(DateInterval.WeekOfYear, IIf(DatePart(DateInterval.Weekday,DateSerial(DatePart("yyyy",Parameters!StartDate_CurrWk.Value),1,1),FirstDayOfWeek.Monday)=1,DatePart(DateInterval.WeekOfYear,Parameters!StartDate_CurrWk.Value,FirstDayOfWeek.Monday),DatePart(DateInterval.WeekOfYear,Parameters!StartDate_CurrWk.Value,FirstDayOfWeek.Monday)-1), DATEADD("d", 1 - DATEPART(DateInterval.WeekDay, DateSerial(DatePart("yyyy",Today())-1, 1, 1), FirstDayOfWeek.Monday),DateSerial(DatePart("yyyy",Today())-1, 1, 1)))

@EndDate_SWLY=DateAdd("d",6,Parameters!StartDate_SWLY.Value)

@StartDate_MTD=DateSerial(DatePart("yyyy",Parameters!EndDate_CurrWk.Value),DatePart("m",Parameters!EndDate_CurrWk.Value), 1)

@EndDate_MTD=Parameters!EndDate_CurrWk.Value

@StartDate_MTDLY==DateSerial(Datepart("yyyy",Parameters!StartDate_MTD.Value)-1,Datepart("m",Parameters!StartDate_MTD.Value),Datepart("d",Parameters!StartDate_MTD.Value))

@EndDate_MTDLY==DateSerial(Datepart("yyyy",Parameters!EndDate_MTD.Value)-1,Datepart("m",Parameters!EndDate_MTD.Value),Datepart("d",Parameters!EndDate_MTD.Value))

@StartDate_YTD=IIf(DatePart("m", Parameters!EndDate_CurrWk.Value)<7, Dateserial(Datepart("yyyy",Parameters!EndDate_CurrWk.Value)-1,7,1), Dateserial(Datepart("yyyy",Parameters!EndDate_CurrWk.Value),7,1))

@EndDate_YTD==Parameters!EndDate_CurrWk.Value

@StartDate_YTDLY=DateSerial(Datepart("yyyy",Parameters!StartDate_YTD.Value)-1,Datepart("m",Parameters!StartDate_YTD.Value),Datepart("d",Parameters!StartDate_YTD.Value))

@EndDate_YTDLY==DateSerial(Datepart("yyyy",Parameters!EndDate_YTD.Value)-1,Datepart("m",Parameters!EndDate_YTD.Value),Datepart("d",Parameters!EndDate_YTD.Value))

@WeekNum==Datepart(DateInterval.WeekOfYear,Parameters!StartDate_CurrWk.Value,FirstDayOfWeek.Monday)

I wonder to know may I do the similar thing in Power BI desktop?

Thanks very much for your time and patient!

Lu

[v-yuta-msft · Accepted solution]

Hi Lu,

Yes, in power bi, we use Data Analysis Expressions (DAX) to solve a number of basic calculation and data analysis problems. There're many time intelligence functions in DAX, for your requirement, please refer to MSDN document:https://msdn.microsoft.com/en-us/query-bi/dax/time-intelligence-functions-dax.

Regards,

Jimmy Tao