Calculate quantity orders last 12 months

[Anonymous]

Hi all,

I want to calculate the quantity orders (column order_ref) over the rolling last 12 months (column order_dat).

If the quantity orders is > 0 , the outcome of the formula must be 1.

If the outcome of the formula = 0, the outcome of the formula must be 0.

Thanks fo your help!

John

[nandukrishnavs · Accepted solution]

@Anonymous 

Could you copy-paste your table here? If possible please share the expected output as well.

[amitchandak · Accepted solution]

You can use something like this date calendar.

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

Or use the relative date filter https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[amitchandak · Accepted solution]

You can use something like this date calendar.

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

Or use the relative date filter https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous · Accepted solution]

She @amitchandak the @nandukrishnavs ,

Problem solved!

Thank you!

[nandukrishnavs]

@jwi1

You have marked my comment as an Accepted Solution. Please mark amitchandak's solution as an Accepted Solution.

[nandukrishnavs · Accepted solution]

@Anonymous 

Could you copy-paste your table here? If possible please share the expected output as well.

[Anonymous]

@nandukrishnavs,

As the quantity orders over the last rolling 12 months = 14, the output must be "1"

partnumber_sup order_ref order_dat order_status_desc order_amount order_amount_total

332805	34793	15-4-2020	Received	10	10
332805	34784	7-4-2020	Received	30	30
332805	34748	10-3-2020	Received	10	10
332805	34682	30-1-2020	Received	10	10
332805	34595	11-12-2019	Received	10	10
332805	34538	18-11-2019	Received	10	10
332805	34504	1-11-2019	Received	10	10
332805	34480	22-10-2019	Received	10	10
332805	34463	16-10-2019	Received	10	10
332805	34436	2-10-2019	Received	10	10
332805	34395	12-9-2019	Received	20	20
332805	34377	3-9-2019	Received	10	10
332805	34350	20-8-2019	Received	10	10
332805	34182	1-5-2019	Received	10	10
332805	34137	1-4-2019	Received	10	10
332805	34099	11-3-2019	Received	10	10
332805	34095	1-3-2019	Received	10	10
332805	34061	11-2-2019	Received	10	10
332805	34038	25-1-2019	Received	10	10
332805	34019	16-1-2019	Received	10	10
332805	34001	4-1-2019	Received	10	10