Calculate delta using slowly changing dimensions

[Janou]

Hi,

I am facing a problem with handling slowly changing dimensions and calculating the delta.

The table looks like this:

Product	Owner	Start date	End date
A	123	1-Jan-24	1-Mar-24
B	234	1-Feb-24	1-Apr-24
C	345	1-Apr-24	1-May-24
D	456	1-Feb-24	1-May-24
E	123	1-Apr-24	1-May-24

The original dates that fill the date columns are monthly, that means: we get new data every month and decide on the start- and end- date every month.

What I would like to achieve is having a table that shows the delta per month, like this:

	Products	Newly added	Removed	Delta
Jan-2024	1	1	0	+1
Feb-2024	3	2	0	+2
Mar-2024	2	0	1	-1
Apr-2024	3	2	1	0
May-2024	0	0	3	-3

How do I get to above result using DAX?

[johnt75 · Accepted solution]

You can create measures like

Products = 
VAR CurrentDate = MAX( 'Date'[Year month] )
VAR Result = CALCULATE(
	COUNTROWS( 'Table' ),
	'Table'[Start date] <= CurrentDate
		&& 'Table'[End date] > CurrentDate
)
RETURN Result

Newly Added = 
VAR CurrentDate = MAX( 'Date'[Year month] )
VAR Result = CALCULATE(
	COUNTROWS( 'Table' ),
	'Table'[Start date] = CurrentDate
)
RETURN Result

Removed = 
VAR CurrentDate = MAX( 'Date'[Year month] )
VAR Result = CALCULATE(
	COUNTROWS( 'Table' ),
	'Table'[End date] = CurrentDate
)
RETURN Result

Delta = [Newly Added] - [Removed]

[v-pagayam-msft · Accepted solution]

Hi @Janou ,

Thank you @johnt75 , for your excellent response! Your approach effectively captures newly added, removed, and delta values per month. 

I’d like to expand on this with a small refinement to ensure products that end within the same month are correctly counted when calculating active products.

1.To ensure proper time intelligence calculations, first create a Date Table:

   DateTable = CALENDAR(DATE(2024,1,1), DATE(2024,12,31))

2.Create Newly added,removed and Delta calculation measures using following queries:

• Newly Added =
  CALCULATE(
      COUNTROWS('Products'),
      FILTER(
          'Products',
          MONTH('Products'[Start Date]) = MONTH(MAX('DateTable'[Date])) &&
          YEAR('Products'[Start Date]) = YEAR(MAX('DateTable'[Date]))
      )
  )
  
  
•  Removed =
  CALCULATE(

      COUNTROWS('Products'),

      FILTER(

          'Products',

          MONTH('Products'[End Date]) = MONTH(MAX('DateTable'[Date])) &&

          YEAR('Products'[End Date]) = YEAR(MAX('DateTable'[Date])) &&

          'Products'[End Date] <= EOMONTH(MAX('DateTable'[Date]), 0)

      )

  )

• Delta = [Newly Added] - [Removed]

• Now,plot a matrix visual then add MonthYear from DateTable to Rows field.

• Add [NewlyAdded], [Removed], and [Delta] to Values filed.
• Now, you will get the desired output.

If our answer meets your requirement,consider accepting it as solution.

Thank you for being a part of Microsoft Fabric Community Forum!

Regards,
Pallavi.

[Janou]

Thanks a lot! The solutions worked! 

[v-pagayam-msft · Accepted solution]

Hi @Janou ,

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

If the issue has been resolved, please mark the helpful reply as a "solution" to indicate that the question has been answered and to assist others in the community.
Thank you for your cooperation. Have a great day.

[v-pagayam-msft · Accepted solution]

Hi @Janou ,

Thank you @johnt75 , for your excellent response! Your approach effectively captures newly added, removed, and delta values per month. 

I’d like to expand on this with a small refinement to ensure products that end within the same month are correctly counted when calculating active products.

1.To ensure proper time intelligence calculations, first create a Date Table:

   DateTable = CALENDAR(DATE(2024,1,1), DATE(2024,12,31))

2.Create Newly added,removed and Delta calculation measures using following queries:

• Newly Added =
  CALCULATE(
      COUNTROWS('Products'),
      FILTER(
          'Products',
          MONTH('Products'[Start Date]) = MONTH(MAX('DateTable'[Date])) &&
          YEAR('Products'[Start Date]) = YEAR(MAX('DateTable'[Date]))
      )
  )
  
  
•  Removed =
  CALCULATE(

      COUNTROWS('Products'),

      FILTER(

          'Products',

          MONTH('Products'[End Date]) = MONTH(MAX('DateTable'[Date])) &&

          YEAR('Products'[End Date]) = YEAR(MAX('DateTable'[Date])) &&

          'Products'[End Date] <= EOMONTH(MAX('DateTable'[Date]), 0)

      )

  )

• Delta = [Newly Added] - [Removed]

• Now,plot a matrix visual then add MonthYear from DateTable to Rows field.

• Add [NewlyAdded], [Removed], and [Delta] to Values filed.
• Now, you will get the desired output.

If our answer meets your requirement,consider accepting it as solution.

Thank you for being a part of Microsoft Fabric Community Forum!

Regards,
Pallavi.

[johnt75 · Accepted solution]

You can create measures like

Products = 
VAR CurrentDate = MAX( 'Date'[Year month] )
VAR Result = CALCULATE(
	COUNTROWS( 'Table' ),
	'Table'[Start date] <= CurrentDate
		&& 'Table'[End date] > CurrentDate
)
RETURN Result

Newly Added = 
VAR CurrentDate = MAX( 'Date'[Year month] )
VAR Result = CALCULATE(
	COUNTROWS( 'Table' ),
	'Table'[Start date] = CurrentDate
)
RETURN Result

Removed = 
VAR CurrentDate = MAX( 'Date'[Year month] )
VAR Result = CALCULATE(
	COUNTROWS( 'Table' ),
	'Table'[End date] = CurrentDate
)
RETURN Result

Delta = [Newly Added] - [Removed]