Forum Discussion
Post ProdigyHow to use two date slicers on different table visuals?
I need to build a report where it compares the same month from different years and the third table shows the percent change. All data is from the same table. so for example:
| Date | A | B | C | Total |
| 4/1/2019 | 45 | 656 | 78 | 779 |
| 4/2/2019 | 36 | 25 | 14 | 75 |
| 4/3/2019 | 74 | 85 | 96 | 255 |
| 1109 | ||||
| Date | A | B | C | Total |
| 4/1/2020 | 54 | 85 | 36 | 175 |
| 4/2/2020 | 45 | 43 | 37 | 125 |
| 4/3/2020 | 81 | 92 | 37 | 210 |
| 510 | ||||
| Date | A | B | C | Total |
| Wed | 10% | 8.50% | 6.30% | 7.90% |
| Thurs | 5.20% | 4.10% | 9.60% | 6.30% |
| Fri | 5.80% | 2.90% | 1.80% | 3.90% |
| 5.10% |
I tried using two seperate slicers with each one filtering a different table visual. But that doesn't seem to work, it comes up blank. I guess it's doing an AND operation on the same data set. Is there any way to do this in BI without creating another table? And how would i create the percent change table with the percent change values? Thanks.
wpf_ As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.
https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/and from this new date table, you can easily get a previous year sum for the same month and then calculate the %, add following measure
Current = SUM ( Table[Value] ) Prev = CALCULATE ( [Current], DATEADD( DateTable[Date], -1, YEAR ) ) % Diff = DIVIDE ( [Current] - [Prev], [Prev] )Not sure if you data exactly looks like the way you showed, if yes, then you need to unpivot your data
- transform data
- select date column
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply- set relationship date from the transaction table with the date table added earlier.
To visualize,
- matrix visual:
- add date from date table on rows,
- add attribute on columns
- add above measure on valuesI would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡
,
3 Replies
- parry2k
Super User
wpf_ As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.
https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/and from this new date table, you can easily get a previous year sum for the same month and then calculate the %, add following measure
Current = SUM ( Table[Value] ) Prev = CALCULATE ( [Current], DATEADD( DateTable[Date], -1, YEAR ) ) % Diff = DIVIDE ( [Current] - [Prev], [Prev] )Not sure if you data exactly looks like the way you showed, if yes, then you need to unpivot your data
- transform data
- select date column
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply- set relationship date from the transaction table with the date table added earlier.
To visualize,
- matrix visual:
- add date from date table on rows,
- add attribute on columns
- add above measure on valuesI would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡
,
wpf_parry2k ,
Thanks very much, i just tried the prev measure and it worked!
Just one issue though. What if i wanted not to go just one year previous in exact calendar date (4/1/2020 to 4/1/2019), but wanted to go one year previous to that same first weekday of that month ( Wednesday 4/1/2020 to Wednesday 4/3/2019) ? Thanks.
- wpf_
parry2k ,
i was able to get to the previous year to the first day of that month using DAY intervals in DATEADD function (minus or plus a few days). I will look into using a variable for the interval days and use a slicer to change the variable. If you have a better solution please let me know. Thanks.