12 months rolling NOT FOR SALES

DAX DAX Calculations
1

Hi ,

I am trying to figure out measure for my task - 12 months rolling average for Incidents.
I have column with unique Incidents numbers and I can not use SUM , I can only Count them.

This measure is not working correctly for this task:

Calculate (
COUNT(FACT_INCIDENT[INCIDENT_NO]),DATESINPERIOD(FACT_INCIDENT[OP_DATE], LASTDATE(FACT_INCIDENT[OP_DATE]), 1, YEAR))
/
CALCULATE(DISTINCTCOUNT(FACT_INCIDENT[OP_DATE]), DATESINPERIOD(FACT_INCIDENT[OP_DATE], LASTDATE(FACT_INCIDENT[OP_DATE]), 1, YEAR))

Other attempts gave me absolute nonsense:

image
image800Ă—803 44.6 KB

Could you please help how to make it right . Sadly, but most of sources are in relation to Sales which you can easily SUM and I can not use - my data comes from Data Warehouse and can expect any changes from it

Many thanks - Iwona

2

Hi @Iwona, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your current work-in-progress PBIX file
  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

I believe I explained clearly my problem ( I did my best at least).
I have uploaded a screenshot with my work . Is this not enough ?

Iwona

4

@Iwona The reason for the pbix file is for the person assisting you to be able to see the whole picture. There could be measure that is conflicting with another one. Hard to say. But looking at your screenshot, you are using statistical aggregating functions such as MAX and MINX. MAX and MAXX behave differently. MAXX evaluates an expression for each row of a table and returns the largest value. MAXX( Table, Table[ColumnName]).

MAX just returns the largest value in a column. MAX ( ColumnName)

Perhaps you need to change the MAX to a MAXX. But again we are just throwing darts into the air without seeing the big picture.

On the rolling average, try using
SAMEPERIODLASTYEAR Date[Date]) instead of last date. If not, should it be -1 instead of 1, YEAR? Is your Fact table have a relationship with your Date Table?

Best of luck to you.

Paul

1 Like
5

Hello @Iwona, good to see that you are having progress with your inquiry. Did the response from @Paul.Gerber help you solve your inquiry?

If it does, kindly mark as solution the answer that solved your query.

If not, how far did you get and what kind of help you need further?

6

Hi @Iwona, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.