Forum Discussion

slyfox's avatar
slyfox
Icon for Helper II rankHelper 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
Icon for Microsoft Employee rankMicrosoft 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
Icon for Helper II rankHelper 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
    Icon for Microsoft Employee rankMicrosoft 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]
		)
    • mrslyfox's avatar
      mrslyfox
      Icon for Helper II rankHelper II
      9 years ago

      Hello Phil_Seamark

       

      Now it is working as expected for Case A.

      How to deal with Case B ?

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi mrslyfox

         

        I think it might be a case of just tweaking the date ranges.  eg. the -3 to -2 as I have highlighed in red

         

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