Difficulty with rolling data (column count)

[Willborn]

Hi there!

I currently have a static solution to count the amount of orders placed for a machine within the last 4 years. For this, I extract the data of the last 4 years and count the unique Purchase Order no. on the machine serial no.  - then filter them to "1". This gives me all Machines, where we had only 1 order within the last 4 years.

Actually, the above solution is working when I extract the data from the 4 year periode only (manually). So I can show only 1 month.  My goal is to make this more dynamic, so I can extract all data from the past 5-6 years, and then be able to visualize the results of the whole fiscal year.

Guess this is possible using DAX measure - but how? I have 1 Date Table, and 1 Order Table. Each line shows the creation date of the order (i.e. 24.02.2014), the position in the order (i.e.0010), the order no. (i.e. 2301234), the machine serial (i.e. 1002). IMHO I have to group the order positions by machine serial no. in PBI Desktop, and then count the number of orders within the month selected-4 years = 1

Does anyone can help me with the DAX code?

Thx and best regards!

Patrick

[v-ljerr-msft]

@Willborn

According to your description, using DISTINCTCOUNT function to create a measure and showing the measure in the report with a Slicer of your Date should meet requirement. See my sample below.

I assume you have a Order table and a Date table like below.

1. Use the formula below to create a measure called OrderAmounts to count the amount of orders placed for a machine.

OrderAmounts = CALCULATE ( DISTINCTCOUNT ( 'Order'[Order NO] ) )

2. Then you should be able to show the amount of orders placed for a machine within the period you selected from the Slicer like below.

Regards

[Greg_Deckler]

Your issue sounds very similar to the one discussed here:

http://community.powerbi.com/t5/Desktop/Show-the-last-X-years-based-on-the-current-Selection-of-the-...

See if that gets you what you need. Essentially, you should be able to use COUNT or COUNTX (instead of SUM/SUMX) and change the FILTER criteria.