Using a dynamic slicer and measures

[Dwalden]

Hello Power BI Community!

I would love some help. I am relatively new to Power BI and attempting to develop some reports. Ultimately, I am attempting to count rows based on a time period and then have another measure that looks at those dates and counts rows based on that comp period. Example, if I were to use a date slicer and say the last 4 weeks, I would like my measure to count these and then another measure that counts the dates from that same period last year. I have one table of data and a date table that I have created. 

Any advice on how to accomplish this would be great! 

[MNedix]

As a rule of thumb, I personally stay away from too much flexibility to be given to the end user. So I wouldn't give the users the ability to use a granular slider (days/weeks) unless it is absolutely necessary. I'd rather go with the major reporting milestones - Month, Quarter, Year.

What I would do in your situation (assuming you have already a Date table:
1. Create the global measure, e.g. Global_Count =CALCULATE(DISTINCTCOUNT(Table[Call_Id]))
2. Create the xTD measures:
- Count_MTD =CALCULATE (Global_Count, DATESMTD ( 'Date'[Date] ))

- Count_QTD =CALCULATE (Global_Count, DATESQTD ( 'Date'[Date] ))

- Count_YTD =CALCULATE (Global_Count, DATESYTD ( 'Date'[Date] ))

3. Go for the past period

- Count_SPLY_MTD = CALCULATE (Count_MTD, SAMEPERIODLASTYEAR ('Date'[Date]))

- ....

I hope it helps.

PS: If it answered your question then please mark it as a solution so others can see.

[Dwalden]

I definitely see where you are coming from. My end users actually are looking for this dynamic capability. Typically, we look at data from various times and it's doesn't really follow basic reporting milestones (this week compared to this week, that week compared to this week, etc). 

[MNedix]

So do you want the ability to (even) select a period in the past and compare it with the same period the year before or just days from the past till present are enough? For example, do you want the period CW2 - CW4 2024 (and then compared to CW2 - CW4 2023) or just 1/2/3/n weeks from today?

[Dwalden]

For this example, we won't have that much data loaded. Currently, we are just looking at two years of data to look at these comp periods. As it relates to your example above, I would set my slicer to say Last 4 Weeks, Last 8 Weeks, Last 13 Weeks. The first measure would count the rows and the second measure would look at the data from the comp periods and display the comp data. 

[MNedix · Accepted solution]

Ok, so let's see if I got this.I had to work with my own data but the logic is still the same. My data went only till 31.12.2023 hence the slider on the top.

Some explanations:

- _F_Months_Back is a slider which counts how many months you wanna go back

- SPLY = Same Period Last Year

The 1st screenshot shows the situation today. Up till now, there have been 967 calls created (the Calls created until box which is the same as the top right reference box). The day is 31.12.2023. From the beginning of time (2021) and last year on 31.12.2022 were created 284 calls. (SPLY = 284).

If we go 1 month back (2nd screenshot) we see that:

- from 01.01.2021 till 30.11.2023 were created 950 calls which means the Delta for December is 17 calls

- from 01.01.2021 till 30.11.2022, the SPLY, is 256 calls which means that in December 2022 28 calls were created (the Delta SPLY)

Same logic for screenshot 3.

To check the logic, we go 12 months back (last screenshot). As you can see, the Calls Created Until box is 284 which is exactly the initial SPLY.

Is this what you were looking for? You can go by days if you want but I thought Months make more sense. I can give you the measures if need be.

[MNedix]

Hi,

Do you really want to use a slicer for the initial measure or do you want it hardcoded. Can your example be rephrased as: "Given that each call is an entry in the table, I want to know how many calls were done in the past 1/2/3.../n weeks and how does this compare to the same period last year"?
If that's the case then a DATEADD / SAMEPERIODLASTYEAR / PARALLELPERIOD combo can give you what you want.

[Dwalden]

You are spot on. I want my slicer to calc my initial measure and then is the above mentioned formulas to do that same period last year.