Last value for each customer

[Anonymous]

Hi,

I have a problem with a measure that should return the last value for a given customer. Sample excel table:

Date	Customer	Measure	Brand
16.09.2021	A	100	x
16.09.2021	B	110	x
16.09.2021	C	120	x
16.09.2021	D	130	x
16.09.2021	D	5	y
16.09.2021	E	140	x
17.09.2021	A	150	x
17.09.2021	B	160	x
17.09.2021	C	170	x
17.09.2021	E	180	x
18.09.2021	A	190	x
18.09.2021	B	200	x
24.09.2021	C	210	x

I wrote the measure below:

SUMX (

VALUES ( Arkusz1[Customer] ),

CALCULATE ( SUM(Arkusz1[Measure] ), LASTDATE ( Arkusz1[Date] ) )

)

It works fine until I turn on aggregation after e.g. weeks. The problem is that if in a given week / other period there is no value for a given customer, I would like it to take from the previous period (so far it returns 0). Is it possible for the measure to be context-sensitive on the one hand and for the blank to take the last non-empty value on the other hand? Now I have:

To sum up: I want the measure to be actually calculated in the context of the week as it is now, but in the case of a blank it should take the last available value for each customer.

Best Regards!

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Here's my solution.

1.Create a calendar table and there's no relationship between two tables.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Weeknum",WEEKNUM([Date],2))

2.Create a Weeknum column in Arkusz1 table.

Weeknum = WEEKNUM([Date],2)

3.Measure 3 is the measure you created.

Measure 2 = SUMX (
VALUES('Arkusz1'[Customer]),
CALCULATE ( SUM('Arkusz1'[Measure]), LASTDATE ( 'Arkusz1'[Date] ) )
)

4.Create the following measure

LastDateByWeeknumWithoutBlank = 
var _value=CALCULATE([Measure 2],FILTER('Arkusz1',[Weeknum]=MAX('Calendar'[Weeknum])))
var _valuelastweek=CALCULATE([Measure 2],FILTER('Arkusz1',[Weeknum]=MAX('Calendar'[Weeknum])-1))
return
IF(_value=BLANK(),_valuelastweek,_value)

5.If you only want to limit the number of weeks to only the number of weeks in your main table.

   Create a flag measure and put it into Filters.

flag = var _max=MAXX(ALL(Arkusz1),[Weeknum])
var _min=MINX(ALL(Arkusz1),[Weeknum])
return IF(_min<=MAX('Calendar'[Weeknum])&&_max>=MAX('Calendar'[Weeknum]),1)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Maybe I will add that the dates on the visualization come from a different date table (calendarauto)

[Greg_Deckler]

@Anonymous Maybe:

New Measure = 
  VAR __Customer = MAX('Table'[Customer])
  VAR __Date = MAX('Table'[Date])
  VAR __Value1 = SUMX ( VALUES ( Arkusz1[Customer] ), CALCULATE ( SUM(Arkusz1[Measure] ), LASTDATE ( Arkusz1[Date] ) )
  VAR __MaxDate = MAXX(FILTER(ALL('Table'),[Customer]=__Customer && [Date]<__Date),[Date])
  VAR __Value2 = SUMX ( VALUES ( Arkusz1[Customer] ), CALCULATE ( SUM(Arkusz1[Measure] ), 'Table'[Date] = __MaxDate ) )
)
RETURN
  IF(ISBLANK(__Value1),__Value2,__Value1)

Something along those lines. 

[Anonymous]

Unfortunately, it doesn't work for me:(

[Anonymous]

Spoiler

Nobody?