Creating last 2 and last 10 days view

DAX DAX Calculations
1

Hi there,

I have a fact table that is connected to the date (dimension table) using the date column. The fact table contains information about events. I created measures that calculate the total no of events, total no of people who visited events, the average length of the event, Average no of people who visited the event, etc.

Now I want to calculate three filters related to date i.e. ‘Today’, ‘in last 2 days’, and ‘Last 10 days’. I want to set up filters in such a way that for example, if I select ‘in last 2 days’ from the slicer, all measures present on the page show the result w.r.t last 2 days.

So, first I created a table that contains all three filter values as shown in the picture:
image

I named the table as ‘Slicer table’. I used the value of the slicer table to generate the slicer visual.

Then I created a measure that selects the value (shows as follow):
User Day Selection = SELECTEDVALUE(‘Slicer Table’[Value])

Then I created a final measure something like as follow:
Calculation day =

SWITCH(
TRUE(),

[User Day Selection] = "Today", IF(DATEDIFF(MAX('Fact Table'[Date]),TODAY(),DAY) = 0,1),
[User Day Selection] = "Within 2 days", IF(DATEDIFF(MAX('Fact Table'[Date]),TODAY(),DAY) <= 2,1),
[User Day Selection] = "Within 10 days", IF(DATEDIFF(MAX('Fact Table'[Date]),TODAY(),DAY) <= 10,1)

)

Now I am bit lost, I am not sure where and how to apply the final measure.

When I googled it, I found measures that can do the similar operations and they applied on particular visual (such as a table, card ) but not on the page level. I am a bit lost and don’t know how to do it. Could anyone help me in that regard?

1 Like
2

Hi @leo_89,

This video showcases the SWITCH TRUE technique.

Side note instead of the Slicer Table, you could explore incorporating a Date Range Slicer.

I hope this is helpful

1 Like
3

@Melissa, Thanks for the reply. Actually I was looking for Date Range Slicer. Post looks very interesting. I have a question related to the post. When we create the table (by following steps mentioned in the blog), I could not get whether the newly created table should be connected to the date table or to the fact table?

4

Hi @leo_89,

The referenced article, covers this so please read carefully and follow all instructions.

image

1 Like
5

@leo_89 You have to use Calculation groups for this kind of analyses.

1 Like
6

Hi @leo_89, we hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

7

Hi @leo_89, did the response provided by @Melissa and @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

8

Hi @leo_89, we’ve noticed that no response has been received from you since the 30th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

9

Hi @leo_89, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!