Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
slyfox
Helper II
Helper II

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 8
Phil_Seamark
Employee
Employee

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

 

april.png

 

If you are happy with these measures, it's a pretty easy tweak to create measures for Case B


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

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]
		)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hello @Phil_Seamark

 

Now it is working as expected for Case A.

How to deal with Case B ?

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]
		)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hello @Phil_Seamark

 

yes, your measure is working as expected,

How to calculate number of callendar days from currently selected 25-Apr till 1st of Feb ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.