topic Re: Manipulate measure and cumulate/show it until specific date in DAX Commands and Tips

Manipulate measure and cumulate/show it until specific date

DAXDisaster — Wed, 31 Jul 2024 14:48:25 GMT

Ciao friends!

I want to do the following with my data

Title	Amount	Creation Date	Close Date
Kawa	100	01.01.2024	15.03.2024
Bent	150	15.02.2024	null

The data in a matrix without any measure definition looks like the following:

After creating a measure like

**bleep**. Sum Amount = CALCULATE( [SUM Amount], FILTER( ALLSELECTED('Date'), 'Date'[Date]<=max('Date'[Date]) ) )

I got the following behavior:

In addition, I would like to show ~~add up the Amount~~ ~~valu~~e the row (Kawa and Bent) per month and repeat it every month until a close date is set.

In the example above, Kawa should be ~~totaled~~ visible up to March (close date 15.03.24), while Bent should occur permanently in every month (including 2025 and ahead).

Is there any chance to adjust my DAX formula to accomplish that? I asked chatgpt and got the following suggestion:

**bleep** Sum Amount v2: VAR MaxDate = MAX('Date'[Date]) RETURN CALCULATE( [SUM Amount], FILTER( 'Example', 'Example'[Creation Date] <= MaxDate && (ISBLANK('Example'[Close Date]) || 'Example'[Close Date] > MaxDate) ) , USERELATIONSHIP('Example'[Close Date], 'Date'[Date]) )

Unfortunately, this did not work:

I am grateful for any advice

Re: Manipulate measure and cumulate/show it until specific date

Anonymous — Thu, 01 Aug 2024 05:22:52 GMT

Hi DAXDisaster ,

Have a good day. I made a sample for you.

Calendar = ADDCOLUMNS( CALENDAR(DATE(2024,1,1) , DATE(2025,1,31)) , "Year Month" , YEAR([Date])*100+MONTH([Date]),"Year", YEAR([Date]) , "Month" , FORMAT([Date],"MMMM") , "MonthValue",MONTH([Date]) )

Measure = var _startYearMonth= YEAR( MIN('Table'[Creation Date]) ) *100 + MONTH( MIN('Table'[Creation Date])) var _CloseYearMonth = YEAR( MAX('Table'[Close Date]) ) *100 + MONTH( MIN('Table'[Close Date])) RETURN IF( MAX('Calendar'[Year Month]) >= _startYearMonth && ( MAX('Calendar'[Year Month]) <= _CloseYearMonth || _CloseYearMonth =BLANK()) ,SUM('Table'[Amount]) ,BLANK())

Measure 2 = IF( NOT( ISINSCOPE('Table'[Title]) ) && ISINSCOPE('Calendar'[Month]) , SUMX( FILTER(ALLSELECTED('Table') , YEAR([Creation Date])*100+ MONTH([Creation Date]) <= MAX('Calendar'[Year Month]) && (YEAR([Close Date])*100+ MONTH([Close Date]) >= MAX('Calendar'[Year Month]) || [Close Date]=BLANK() ) ) , [Amount]) , [Measure])

Best Regards,

Wearsky

Re: Manipulate measure and cumulate/show it until specific date

DAXDisaster — Thu, 01 Aug 2024 08:19:16 GMT

Hi Anonymous ,
thanks for your help.

It works so far, but as you pointed out, I have to delete the connection between my fact table (example) and the calendar. Is there any chance to accomplish the behavior with an active connection on create date and an inactive connection for closed date?

I need the connection for other use cases 😞

Re: Manipulate measure and cumulate/show it until specific date

Anonymous — Thu, 01 Aug 2024 09:14:08 GMT

Hi DAXDisaster ,

Thanks for your feedback. Please try to create a new calculated table as the third-row parameter of the Matrix visual.

Table 2 = ALL('Table'[Title])

Measure 4 = VAR _start = CALCULATE ( MAXX ( FILTER ( 'Table', [Title] = MAX ( 'Table 2'[Title] ) ), [Creation Date] ), ALL ( 'Calendar' ) ) VAR _end = CALCULATE ( MAXX ( FILTER ( 'Table', [Title] = MAX ( 'Table 2'[Title] ) ), [Close Date] ), ALL ( 'Calendar' ) ) VAR _startYearMonth = YEAR ( _start ) * 100 + MONTH ( _start ) VAR _CloseYearMonth = YEAR ( _end ) * 100 + MONTH ( _end ) VAR _v = CALCULATE ( SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Amount] ), ALL ( 'Calendar' ) ) RETURN IF ( MAX ( 'Calendar'[Year Month] ) >= _startYearMonth && ( MAX ( 'Calendar'[Year Month] ) <= _CloseYearMonth || _CloseYearMonth = BLANK () ), _v, BLANK () )

Measure 5 IF ( ISINSCOPE ( 'Calendar'[Month] ) && NOT ( ISINSCOPE ( 'Table 2'[Title] ) ), SUMX ( FILTER ( ALLSELECTED ( 'Table' ), YEAR ( [Creation Date] ) * 100 + MONTH ( [Creation Date] ) <= MAX ( 'Calendar'[Year Month] ) && ( YEAR ( [Close Date] ) * 100 + MONTH ( [Close Date] ) >= MAX ( 'Calendar'[Year Month] ) || [Close Date] = BLANK () ) ), [Amount] ), [Measure 4] )

Best Regards,

Wearsky

Re: Manipulate measure and cumulate/show it until specific date

DAXDisaster — Fri, 02 Aug 2024 06:55:15 GMT

Oh boy,
that has helped me so much Anonymous . I'm a bit embarrassed to ask for more help. I'm also trying to implement the following:
On the lines I need the Created and Closed Date per Title (the last one only if available, like in my example picture visibile in Measure 9)
In addition, I need a measure that performs a count per title row and totals the values per month (Measure 10).

I have minimally adapted Measure 8 from your example (Measure 4) and hope that this is correct so far. I have also added a third row of data. Thanks again for your help so far.

Measure 8 = VAR _start = CALCULATE ( MAXX ( FILTER ( 'Example 2', [Title] = MAX ( 'Table 2'[Title] ) ), [Creation Date] ), ALL ( 'Date' ) ) VAR _end = CALCULATE ( MAXX ( FILTER ( 'Example 2', [Title] = MAX ( 'Table 2'[Title] ) ), [Close Date] ), ALL ( 'Date' ) ) VAR _startYearMonth = YEAR ( _start ) * 100 + MONTH ( _start ) VAR _CloseYearMonth = YEAR ( _end ) * 100 + MONTH ( _end ) VAR _v = CALCULATE ( MAXX ( FILTER ( ALLSELECTED ( 'Example 2' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Creation Date] ), ALL ( 'Date' ) ) RETURN IF ( MAX ( 'Date'[Year-Month Code] ) >= _startYearMonth && ( MAX ('Date'[Year-Month Code] ) <= _CloseYearMonth || _CloseYearMonth = BLANK () ), IF( ( ISINSCOPE ( 'Table 2'[Title] ) ) ,_v, BLANK () ) )

New row:

Title	Creation Date	Close Date	Amount
Bent	15.02.2024		150
Kawa	01.01.2024	15.03.2024	100
Harl	28.02.2024	29.04.2024	200

Re: Manipulate measure and cumulate/show it until specific date

Anonymous — Mon, 05 Aug 2024 01:41:52 GMT

Hi DAXDisaster ,

Thanks for your feedback. I made a sample for you.

Measure 6 = VAR _start = MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Creation Date] ) VAR _end = MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Close Date] ) VAR _startYearMonth = YEAR ( _start ) * 100 + MONTH ( _start ) VAR _CloseYearMonth = YEAR ( _end ) * 100 + MONTH ( _end ) VAR _v = CALCULATE ( SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Amount] ), ALL ( 'Calendar' ) ) RETURN IF ( ISINSCOPE ( 'Table 2'[Title] ), IF ( MAX ( 'Calendar'[Year Month] ) >= _startYearMonth && ( MAX ( 'Calendar'[Year Month] ) <= _CloseYearMonth || _CloseYearMonth = BLANK () ), FORMAT ( _start, "dd.mm.yyyy" ), BLANK () ), BLANK () )

Measure 7= VAR _start = MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Creation Date] ) VAR _end = MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Close Date] ) VAR _startYearMonth = YEAR ( _start ) * 100 + MONTH ( _start ) VAR _CloseYearMonth = YEAR ( _end ) * 100 + MONTH ( _end ) VAR _v = CALCULATE ( SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Amount] ), ALL ( 'Calendar' ) ) RETURN IF ( ISINSCOPE ( 'Table 2'[Title] ), IF ( MAX ( 'Calendar'[Year Month] ) >= _startYearMonth && ( MAX ( 'Calendar'[Year Month] ) <= _CloseYearMonth || _CloseYearMonth = BLANK () ), FORMAT ( _end, "dd.mm.yyyy" ), BLANK () ), BLANK () )

Measure 8 = VAR _start = MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Creation Date] ) VAR _end = MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Close Date] ) VAR _startYearMonth = YEAR ( _start ) * 100 + MONTH ( _start ) VAR _CloseYearMonth = YEAR ( _end ) * 100 + MONTH ( _end ) VAR _v = CALCULATE ( SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Title] = MAX ( 'Table 2'[Title] ) ), [Amount] ), ALL ( 'Calendar' ) ) RETURN IF ( ISINSCOPE ( 'Table 2'[Title] ), IF ( MAX ( 'Calendar'[Year Month] ) >= _startYearMonth && ( MAX ( 'Calendar'[Year Month] ) <= _CloseYearMonth || _CloseYearMonth = BLANK () ), 1, BLANK () ), IF ( ISINSCOPE ( 'Calendar'[Month] ), COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), ( YEAR ( 'Table'[Creation Date] ) * 100 + MONTH ( 'Table'[Creation Date] ) ) <= MAX ( 'Calendar'[Year Month] ) && ( ( YEAR ( 'Table'[Close Date] ) * 100 + MONTH ( 'Table'[Close Date] ) ) >= MAX ( 'Calendar'[Year Month] ) || 'Table'[Close Date] = BLANK () ) ) ) ) )

Result:

Best Regards,

Wearsky

Re: Manipulate measure and cumulate/show it until specific date

DAXDisaster — Mon, 05 Aug 2024 09:54:32 GMT

Thanks again Anonymous
I really appreciate your help.

My usecase probably looks very weird from an outside perspective. Nevertheless, an additional question: is it possible to have a time filter (slicer) on the page (the active connection from the table (Example) to the Date dimension (via the Create Date)).
Currently, a filter operation would cause the value of the measure to disappear, but the row with the name would remain. My goal is that, for example, when filtering from February onwards, only the values from February onwards are visible (in my example, Kawa (start date: 01.01.2024) should not displayed).

Re: Manipulate measure and cumulate/show it until specific date

Anonymous — Fri, 23 Aug 2024 06:58:13 GMT

Hi DAXDisaster ,

Sorry for the delay reply.

This looks like a new question so I suggest you post a new thread.

Best Regards,

Wearsky