Forum Discussion

ROG's avatar
ROG
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

Measure using another measure minus previous date.

Hi all,

I would like to add another column the table below that show me the 'Opening Base'.
I know that the  'Opening Base' is sum(active_customers) from fsubscriber_table on the last day of the previous month.
Basically on the table below, the Active Customers value 733,719 from the 31st of Jan 2022 should be the value of the 'Opening Base' on the 1st of Feb 2022.
I'm struggling to get this logic in a measure. 

Many thanks.

Need Help
  • ROG's avatar
    ROG
    4 years ago

    Hey, that works! Thank you.

2 Replies

  • vapid128's avatar
    vapid128
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    add colnum:

    YYMM = YEAR([date])*12+MONTH([date])
     
    Measure 2 =
    var _lastDate = CALCULATE(MAX([date]),FILTER(ALL(table22), table22[YYMM]= MAX(table22[YYMM])-1))
    return CALCULATE([Measure],FILTER(ALL(table22),table22[date]=_lastDate))
     
    Measure 3 =
    IF(DAY( MIN(table22[date]))=1,
    [Measure 2],
    [Measure]
    )
    • ROG's avatar
      ROG
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

      Hey, that works! Thank you.