CODEWITHSUNDEEP
powerbidax
db_dora_1234
db_dora_1234

Reputation: 15

Power BI DAX formula doesn't work as expected

I need to develop DAX formula that calculates revenue of new customers. New customers are calculated so: Revenue for current (selected) period is greater or equal to 0.01 and revenue for previous period is less than 0.01 or greater than -0.01. There is slicer with values: current period, previous, Nov-23 and etc. But current revenue needs to be calculated for 12 rolling months, starting from last date of the selected period (if selected previous period, it is 31.12.2023). For revenue of previous period it is respectively 1.01.2022 - 31.12.2022 (12 rolling months) I developed this formula:

Revenue = 
VAR _CurrentPeriodEnd =
    IF (
        ISFILTERED ( 'Time'[Month] ) = TRUE (),
        MAX ( 'Time'[Day] )
    )
VAR _CurrentPeriodBegin =
    EDATE ( _CurrentPeriodEnd, -12 ) + 1
VAR _PreviousPeriodEnd =
    IF (
        ISFILTERED ( 'Time'[Month] ) = TRUE (),
        EDATE ( _CurrentPeriodEnd, -12 ),
        EDATE ( _CurrentPeriodEnd, -12 )
    )
VAR _PreviousPeriodBegin =
    EDATE ( _PreviousPeriodEnd, -12 ) + 1
VAR _tblRevenue =
    ADDCOLUMNS (
        VALUES ( 'Customers'[Customer] ),
        "FilterCP",
            CALCULATE (
                [Revenues],
                'Time'[Day] >= _CurrentPeriodBegin,
                'Time'[Day] <= _CurrentPeriodEnd
            ),
        "FilterPP",
            CALCULATE (
                [Revenues],
                'Time'[Day] >= _PreviousPeriodBegin,
                'Time'[Day] <= _PreviousPeriodEnd
            )
    )
VAR _tblPYRevenueBelowZero =
   CALCULATE(SUMX(_tblRevenue, [FilterCP]), FILTER(_tblRevenue, [FilterCP] >= 0.01 && [FilterPP]<0.01 && [FilterPP]>-0.01))
RETURN
    _tblPYRevenueBelowZero

I expect it to show revenue for current period of new customers, but it doesn't work. Separately, it is calculating revenue for previous 12 rolling months and current correctly, but this comparison (e.g. FilterCP>=0.01) doesn't work, it seems to me.

Upvotes: 0

Views: 107

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

See if this helps:

VAR _tblPYRevenueBelowZero =
  SUMX(
    _tblRevenue,
      var cp = CALCULATE([FilterCP])
      var pp = CALCULATE([FilterPP])
      return IF(cp >= 0.01 && pp < 0.01 && pp > -0.01, [FilterCP])
      // or return IF(cp >= 0.01 && pp < 0.01 && pp > -0.01, cp)
  )

Upvotes: 0

Related Questions