Forum Discussion
Using Measure to Compare Current Period to Previous Period
Hi Everyone,
I am currently using the measure below to compare the current period and the previous period, but since the measure is going back by number of days I am running into a problem. For example, June 1-June 30th is the current period. The previous period will show May 1st to May 30th, but leave out May 31st because the measure goes back by the number of days, not by month.
Can this measure be modified to show the previous period as a complete month?
The reason why I choose to use this measure over an alternative measure is that I can easily change the filter on the page to show month vs month, quarter vs quarter, and year vs year, and all the visuals will update to reflect those changes. I can make measures to show those time ranges, but I would rather not if I can get this measure to work properly.
TIA!
Visitors for Previous Period =
VAR PeriodEnd = FIRSTDATE('Date'[Date])-1
VAR PeriodStart =
PeriodEnd -
DATEDIFF(
FIRSTDATE('Date'[Date]),
LASTDATE('Date'[Date]),
DAY
)
VAR Result =
CALCULATE(
[Sum of Sessions],
DATESBETWEEN('Date'[Date],
PeriodStart,PeriodEnd
)
)
Return
Result
Hi Everyone,
Here is the solution that I have found to work. I hope someone finds this useful. I will give credit to the freelancer who came up with this at the end of the post.
End Result:
You will have one slicer for the current period and one slicer for the previous period. You can compare any range of dates to one another by selecting your date range in the corresponding slicer. In the photo below the current period slicer is showing 6/1/2021-6/30/2021 and the previous period slicer is showing 5/1/2021-5/31/2021. The light blue line is showing the current period and the dark blue line is showing the previous period in the visuals.Step 1:
Create a new measure called "Previous Date Selector" and use your date table as the parameter value.
Step 2:
Create an inactive one too many relationship between your "Previous Date Selector" and regular date table.
Step 3:
Create a slicer from your standard date table and name it "current period" and create a slicer from your "previous date selector" and name it "previous period."
Step 4:
Create a measure with the following dax. In this case, I am comparing total sessions in the current period to total sessions in the previous period so I am using the "total sessions" value.
Step 5
Add your two values to the visual you would like to use to compare the current period to the previous period. In this case, I am comparing total sessions from google analytics so I have a measure for "total sessions", which is synched to the current period slicer and a measure for "total visitors for previous period", which is synched to the previous period slicer.
I hope this helps someone!
Freelancer: andystepas | Profile | Fiverr
10 Replies
Hi Everyone,
Here is the solution that I have found to work. I hope someone finds this useful. I will give credit to the freelancer who came up with this at the end of the post.
End Result:
You will have one slicer for the current period and one slicer for the previous period. You can compare any range of dates to one another by selecting your date range in the corresponding slicer. In the photo below the current period slicer is showing 6/1/2021-6/30/2021 and the previous period slicer is showing 5/1/2021-5/31/2021. The light blue line is showing the current period and the dark blue line is showing the previous period in the visuals.Step 1:
Create a new measure called "Previous Date Selector" and use your date table as the parameter value.
Step 2:
Create an inactive one too many relationship between your "Previous Date Selector" and regular date table.
Step 3:
Create a slicer from your standard date table and name it "current period" and create a slicer from your "previous date selector" and name it "previous period."
Step 4:
Create a measure with the following dax. In this case, I am comparing total sessions in the current period to total sessions in the previous period so I am using the "total sessions" value.
Step 5
Add your two values to the visual you would like to use to compare the current period to the previous period. In this case, I am comparing total sessions from google analytics so I have a measure for "total sessions", which is synched to the current period slicer and a measure for "total visitors for previous period", which is synched to the previous period slicer.
I hope this helps someone!
Freelancer: andystepas | Profile | Fiverr
Hi,
this is exactly what I am trying to achieve but I am having an issue with step 1: "Create a new measure called "Previous Date Selector" and use your date table as the parameter value."
My date field doesn't show up as a date in the parameter and it looks like I cannot use it for anything.
This is an old post but I hope someone can help me figure out what might be wrong.
Thanks!
Anonymous I think you are over-engineering the problem. There are way too many solution available to achieve MoM/QoQ/YoY based on the slicer selection, like calculation groups or you can use Row Based time intelligence by following this blog post Row-based Time Intelligence - Phil Seamark on DAX
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi parry2k ,
I have considered creating measures for a monthly, quarterly, and yearly comparison, but the problem I foresee with this method is when management says they want to see a quarterly comparison instead of a monthly comparison, all the measures will have to be switched out on the visual to show the new time comparison. I would like to have the ability to specify a date range and then show the previous period for that specific date range. This will make the entire report dynamic and eliminate the need for a measure for each time range. The measure above works almost perfectly, but the current period and previous period are compared by days, so if there are 30 days in one month and 31 days in another month, one of the months will either be missing a day or have an extra day. I am wondering if you have a suggestion on how to turn this measure into a monthly comparison.Hi parry2k,
What do you think about the solution above?
If you think it can be useful please consider accepting it as a solution.Thank you,
Joshua Cortigiano
Anonymous there is no concept of almost perfectly, it is working or not, you have to use calculation group, maybe with 4 calculation items:
MoM
QoQ
YoQ
Custom
The top 3 are self-explanatory, and in the 4th one, use the range from date slicer and then do the comparison for the same period as you are doing now.
There are TONS of solutions around this and what I suggested above, I have used more than 100 times, not sure if you can take it from here or not.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi parry2k ,
I am not opposed to using those time intelligence calculations, but the DAX expression that I have posted provides more flexibility because you can compare any period to the exact same time range over the previous period by adjusting the slicer. It is very convenient and very useful for reports that need to be regularly changed to compare unique time periods. I have illustrated the issue that is still persisting below. You will see that the previous period is showing 5/1/2021 - 5/30/2021, but it should show 5/1/2021-5/31/2021. I am still wondering if there's a way to modify this formula so the previous period shows the entire month, instead of just showing the number of days that are in the current period.
Anonymous there is no point beating the bushes, seems like you are again overcomplicating the calculations. Anyhow, I hope someone can help and walk you thru.
Anonymous if this works for you good but not sure I will go that route, it means the user always has to select a value in both the slicers to compare. Not sure if it is a great UX but if it solves your needs, well done.
Cheers,
P
Hi,
This should work
Visitors for previous period = calculate([sum of sessions],previousmonth('Date'[Date]))
