1

Calculation based on Date Filter Range

I am looking for a calculation of a value divided by month; however, the number of months is dynamic depending on the overall date range that the user has set. For example, if the user set a range of February to May (total of four months), then the calculation would be VAL / 4. If the user changed that range to January to June, then the calculated field would update to VAL / 6.

Parameters can allow for a number of months to be designated, but I want to sync it up with the date filter. Is there any way to do that?

7 replies

null
    • Customer Solutions Architect
    • Moshe_Yossef
    • 4 mths ago
    • Reported - view

    The easy option is to create a granular measure in the model that will be a count distinct on the months (or actually on the full month name or something like that).

    this measure will always aggregate to the total number of months in the context of the selection (notice on which table you use to create it - there's a difference between the dates table and a fact table).

    then you can use any type of filter and divide any measure by this "months" measure.

     

    let me know if it helps.

    Moshe

      • John_Fonte
      • 4 mths ago
      • Reported - view

       It definitely does help. The key thing I didn't know was that the distinct month count would update based on the selection - I thought it would just be a hardcoded static value in the model. Thank you!

      • Customer Solutions Architect
      • Moshe_Yossef
      • 4 mths ago
      • Reported - view

       happy to help.

      Any granular measure is defined the context of the model, it can be very helpful.

    • John_Fonte
    • 4 mths ago
    • Reported - view

    Hi Moshe, 

    This method only works if there is data for every month. However, if I have sales for April, May,  not June, and July, the distinct count will only count those months that appear in the data, making the average sales / 3 months instead of sales / 4 months. It is quite common in my dataset to have whole months with zero sales, so a simple total divided by months counted does not work. 

    (and btw, I tried an IfNull(sales, 0) function to fill in the gaps, but that doesn't work).

    Until I can solve this limitation, this question is not fully answered.

      • John_Fonte
      • 4 mths ago
      • Reported - view

       

      • Customer Solutions Architect
      • Moshe_Yossef
      • 3 mths ago
      • Reported - view

       

      Sorry I missed this notification.

      You can add a dates dimension that will have all the months and dates.

    • NPANS
    • 4 mths ago
    • Reported - view

     Build a complete and separate date table that joins to the facts (turn on bi-directional). Either count the months off this table or sum a counter column (using a "1") on the table.  

    There is a widget in the data flow for creating such tables if you don't have one. If you're using a direct query approach, then manufacture one with views (hard to do). 

Content aside

  • Status Answered
  • 1 Likes
  • 3 mths agoLast active
  • 7Replies
  • 104Views
  • 3 Following