Forum Discussion

murugesh_c's avatar
murugesh_c
Frequent Visitor
3 years ago
Solved

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

  • 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_c's avatar
      murugesh_c
      Frequent Visitor

      Awesome. It works!
      Thanks for drafting the solution in detail!