Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

R12 Calculation month error

I need to do a R12 calculation that counts the number of rows for the past 12 months. I also do a R12 calculation on another measure, this way the measure is done for the past 12 months. Both of the measures are below:

R12 measure = CALCULATE([DE NPS], DATESINPERIOD( Query1[Date], MAX(Query1[Date]), -12, MONTH))
 
R12 row count=CALCULATE(COUNTA(Query1[score]), Query1[question] in {"Dealer Experience"}, DATESINPERIOD( Query1[Date], MAX(Query1[Date]), -12, MONTH))
 
The issue I'm having is when the row count is filtered down to a dealer that doesn't have many inputs and for example, doesn't have any scores submitted for December, the R12 row count measure (for February) will basically skip December and go back 13 months. Similarly if the dealer doesn't have scores submitted for December and October for example, the measure counts back 14 months and skips December and October.
 
Is there a way I can either create a measure that will enter a blank score for the dealer for the months it doesn't have any inputs, or edit the R12 measure so that if there isn't an input for a month it just counts it as 0 instead of skipping the month entirely. Hopefully this makes sense I tried my best to explain!
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , Use date table, if you do not use a date table with continuous dates, you might get error with filters

     

    Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

     

    refer

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you, I created a date table but now I am having an issue where the measure shows a different number if I put a relative date filter for the past 12 calendar months. When I put this relative date filter on the graph it shows the correct score, is there a way I can have the measure do the past 12 calendar months? I think there must be something wrong the measure listed in my initial post. (This score is also going into a table that is why I need this filter in the measure)

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Use a disconnected calendar for the windowing, and then a measure to calculate the values.