Forum Discussion
previous month
Hello,
I have two linked tables, Fact_Sales and Dim_Callendar
Requred a mesure with average calculation for the:
Case A:
Sum of Sales Qty for the last three months excluding current, divided by number of calendar days of those months.
Example:
January 31 days 900 Qty
February 28 days 1000 Qty
March 31 days 1200 Qty
Expected result for any date of April = (900+1000+1200)/ (31+28+31)
Case B:
Sum of Sales Qty for the last three months including current, divided by number of calendar days of those months.
Example:
February 28 days 1000 Qty
March 31 days 1200 Qty
April 30 days MTD qty is 400
Today 24-Apr, and Expected result for any date of April = (1000+1200+400)/ (31+28+31)
8 Replies
Hi slyfox
For part 1 I created the following calculated measures
Sum of Last Three Months = SUMX( DATESINPERIOD( Dim_Calendar[Date], DATEADD(LASTDATE('Dim_Calendar'[Date]),-1,MONTH), -3, MONTH), [Total Amount] )and
Count of days Last Three Months = COUNTROWS( DATESINPERIOD( Dim_Calendar[Date], DATEADD(LASTDATE('Dim_Calendar'[Date]),-1,MONTH), -3, MONTH) )and finally
Case A = DIVIDE([Sum of Last Three Months],[Count of days Last Three Months],0)
If you drag these measures to a grid you can see if they are reporting the numbers you are happy with
If you are happy with these measures, it's a pretty easy tweak to create measures for Case B
Hello Phil_Seamark
Maually calculated Sum of Last Three Months for one of the customers gives me result 1 850 992.646
The Formula
Sum of Last Three Months:=SUMX( DATESINPERIOD(D_Date[LINK_Date], DATEADD(LASTDATE(D_Date[LINK_Date]),-1,MONTH), -3, MONTH),[Sum of IVCL_GrossSqm])
Showing 1 791 046.873
Hi mrslyfox
I only tested these on a very very small dataset. Any chance you can give me a longer data set?
