Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Last value for each customer

Hi,

 

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

DateCustomerMeasureBrand
16.09.2021A100x
16.09.2021B110x
16.09.2021C120x
16.09.2021D130x
16.09.2021D5y
16.09.2021E140x
17.09.2021A150x
17.09.2021B160x
17.09.2021C170x
17.09.2021E180x
18.09.2021A190x
18.09.2021B200x
24.09.2021C210x

 

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!
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Unfortunately, it doesn't work for me:(

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago
        Spoiler
        Nobody?
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.