Forum Discussion
Slicer to compare data with previous months
Friends,
I want to compare previous months data for the selected days on the days slicer. For example, if I choose 1 to 12 days in the days slicer, then the first 12 days result of every month should show on the table. I truly appreciate your time on suggesting me a solution for this. Thanks.
I am Not sure the intention of the slicer, but you can try like this.
a) Create a new parameter with numeric range
1 to 31 with increment 1
Change the slicer as range
b) Optional: To test, you can do this measure
Selected Days = MIN('Days input'[Days input]) & " - " & MAX ('Days input'[Days input])Create a card using this measure "Selected Days"
See if the slicer and the measure works correctly! it should match based on your selection.
c) Assuming
c. 1. your Fact/transaction table and date table are created and relationships are set properly.
c. 2. 'Dim Date' table has day column. We use this column to filter.
c. 3. Create new measure like this:
Count Rows - Filter Days Input = var _begin = Min('Days input'[Days input]) var _end = Max('Days input'[Days input]) return CALCULATE(countrows('Fact Sales'), -- Replace countrows with sum, count or ... as per your needs Filter('Dim Date', 'Dim Date'[Day] >= _begin && 'Dim Date'[Day] <= _end) )Adjust the formula to your needs. i.e., countrows('Fact Sales') to meet your needs like sum, count ... and, change the 'Dim Date' table with your date table.
c.4. Use this measure in your matrix
Hope this help!
3 Replies
- sevenhills
Super User
I am Not sure the intention of the slicer, but you can try like this.
a) Create a new parameter with numeric range
1 to 31 with increment 1
Change the slicer as range
b) Optional: To test, you can do this measure
Selected Days = MIN('Days input'[Days input]) & " - " & MAX ('Days input'[Days input])Create a card using this measure "Selected Days"
See if the slicer and the measure works correctly! it should match based on your selection.
c) Assuming
c. 1. your Fact/transaction table and date table are created and relationships are set properly.
c. 2. 'Dim Date' table has day column. We use this column to filter.
c. 3. Create new measure like this:
Count Rows - Filter Days Input = var _begin = Min('Days input'[Days input]) var _end = Max('Days input'[Days input]) return CALCULATE(countrows('Fact Sales'), -- Replace countrows with sum, count or ... as per your needs Filter('Dim Date', 'Dim Date'[Day] >= _begin && 'Dim Date'[Day] <= _end) )Adjust the formula to your needs. i.e., countrows('Fact Sales') to meet your needs like sum, count ... and, change the 'Dim Date' table with your date table.
c.4. Use this measure in your matrix
Hope this help!
- murugesh_cFrequent Visitor
Awesome. It works!
Thanks for drafting the solution in detail!- sevenhills
Super User
Glad to hear that it worked. Thanks