Forum Discussion

slyfox's avatar
slyfox
Helper II
9 years ago

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 n...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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

mrslyfox's avatar
mrslyfox
Helper II
9 years ago

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

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi mrslyfox

     

    I only tested these on a very very small dataset.  Any chance you can give me a longer data set?

    • mrslyfox's avatar
      mrslyfox
      Helper II
      9 years ago

      Hello Phil_Seamark

      Measure calculated as expected only if select last day of April.

      It mean If I click of D_Date.DatyNumberInMonth slicer 10-Apr, the measure period would be shifted.

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Aha, I see what is happening

         

        Want to give this a test?  I've highlighted the function to change in red.  Let me know how it goes

         

        Sum of Last Three Months = 
		SUMX(
				DATESINPERIOD(
					Dim_Calendar[Date],
					DATEADD(STARTOFMONTH('Dim_Calendar'[Date]),-3,MONTH),
					3,
					MONTH),
					[Total Amount]
		)