Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |