Forum Discussion

Janou's avatar
Janou
New Member
1 year ago
Solved

Calculate delta using slowly changing dimensions

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's avatar
    johnt75
    1 year ago

    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]
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     
     
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    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]
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

     
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.