calculate measure between max date and max date -28

[Arkhos94]

I have a database with the orders my company receive, with one line per order and one column with the date of each order

I want to calculate the number of order per 4 rolling week, starting from the last order (so with a date between the maximum order[date] and the maximum order[date] minus 28 days)

First I tried to create a measure :

OrderRolling = calculate (sum(order[Number of unit ordered]),order[date]<=max(order[date]),order[date]>=max(order[date])-28)

It did not work (the measure refuse to let me use max as a filter value of my calculate)

I try to get arround it by created a measure called lastorder (lastorder=max(order[date])) and put it instead of max(order[date]) in OrderRolling measure but it did not work either

Then I tried to find a way to create in power query a new column that will be Ok if the Date column is between max(order[date]) and max(order[date]) - 28 days but I coudn't find the correct function

Any help now would be welcome

[Anonymous · Accepted solution]

Hi @Arkhos94 

You can use DATESINPERIOD DAX to count the number between max date and max date-28 .Use date column to create a slicer and then create a measure .

Measure = CALCULATE(COUNTROWS('Table'),DATESINPERIOD('Table'[Date],MAX('Table'[Date]),-28,DAY))

Original data:

The effect is as shown :

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @Arkhos94 

Please check the below picture and the sample pbix file's link down below.

I tried to create the sample pbix file based on the explanation.

I suggest having a DimDate table like below. Then time intelligence calculation will become much more easy.

Orders Count 28 days rolling =

SUMX (

DATESINPERIOD ( DimDate[Date], LASTDATE ( DimDate[Date] ), -28, DAY ),

[Order Count]

)

https://www.dropbox.com/s/yvvavuajpzvkq6m/arkhos94.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[PaulDBrown]

@Arkhos94 

1) Create a calendar table using the CALENDARAUTO. Let's call this table Calendar Table. In the modeling view, Link the Calendar [Date] field to your Order[Date] field in a One-to-many relationship.

2) Create the measure:

OrderRolling =
CALCULATE (
    SUM ( order[Number of unit ordered] ),
    DATESBETWEEN (
        Calendar[Date],
        MAX ( Calendar[Date] ) - 28,
        MAX ( Calendar[Date] )
    )
)

Set up the visual using the date field from the Calendar Table and add the measure.