How do I show the results of a measure 3 months after the start date (chosen dynamically on a slicer) on a bar graph in Power BI?

huangapple go评论73阅读模式
英文:

How do I show the results of a measure 3 months after the start date (chosen dynamically on a slicer) on a bar graph in Power BI?

问题

I am trying to provide an analysis of the number of lapsed and retained customers per month on a bar graph.

There are two tables in play -

  1. A Calendar table, the date column of which is used to create the x-axis of a bar graph
  2. A Sales data table, the 'Sale Date' column of which is used to create the date slicer

The two tables have an established relationship based on the respective date columns. This is what the graph looks and slicer looks like -

How do I show the results of a measure 3 months after the start date (chosen dynamically on a slicer) on a bar graph in Power BI?

The lapsed customer count comes from the formula given below


VAR Before6 =
    CALCULATETABLE (
        VALUES ('Master File'[UniqueIDFinal]),
        PARALLELPERIOD ( 'Calendar Table'[Date], -3, MONTH )
    )
VAR During6 =
    CALCULATETABLE (
        VALUES ('Master File'[UniqueIDFinal]),
        DATESINPERIOD (
            'Calendar Table'[Date],
            EOMONTH ( MIN ('Master File'[Sale Date II]), 0 ),
            -3,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ('Master File'[UniqueIDFinal]),
        FILTER (
            ALL ('Master File'),
            'Master File'[UniqueIDFinal] IN Before6
                && NOT 'Master File'[UniqueIDFinal] IN During6
        )
    )*-1

The Retained customer count is a subtraction of the Lapsed customer count from the number of customers served in a month.

The Lapsed customers only begin to show 3 months after the start of the date range in the slicer, which is how the formula is supposed to work.

Is there a way I can show the Retained customer count to show 3 months after the start of the date range as well?

I realize that both formulae are working as expected. However, I would like the Retained customer count to show 3 months after the start of the date range as well as the Retained count should only come into play once customers start lapsing (3 months after the start of the date range as per the Lapsed formula).

Thanks in advance.

英文:

I am trying to provide an analysis of the number of lapsed and retained customers per month on a bar graph.

There are two tables in play -

  1. A Calendar table, the date column of which is used to create the x-axis of a bar graph
  2. A Sales data table, the 'Sale Date' column of which is used to create the date slicer

The two tables have an established relationship based on the respective date columns. This is what the graph looks and slicer looks like -

How do I show the results of a measure 3 months after the start date (chosen dynamically on a slicer) on a bar graph in Power BI?

The lapsed customer count comes from the formula given below

Retention Analysis - Num Lapsed = 

VAR Before6 =
    CALCULATETABLE (
        VALUES ('Master File'[UniqueIDFinal]),
        PARALLELPERIOD ( 'Calendar Table'[Date], -3, MONTH )
    )
VAR During6 =
    CALCULATETABLE (
        VALUES ('Master File'[UniqueIDFinal]),
        DATESINPERIOD (
            'Calendar Table'[Date],
            EOMONTH ( MIN ('Master File'[Sale Date II]), 0 ),
            -3,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ('Master File'[UniqueIDFinal]),
        FILTER (
            ALL ('Master File'),
            'Master File'[UniqueIDFinal] IN Before6
                && NOT 'Master File'[UniqueIDFinal] IN During6
        )
    )*-1

The Retained customer count is a subtraction of the Lapsed customer count from the number of customers served in a month.

The Lapsed customers only begin to show 3 months after the start of the date range in the slicer, which is how the formula is supposed to work.

Is there a way I can show the Retained customer count to show 3 months after the start of the date range as well?

I realise that both formulae are working as expected. However, I would like the Retained customer count to show 3 months after the start of the date range as well as the Retained count should only come into play once customers start lapsing (3 months after the start of the date range as per the Lapsed formula).

Thanks in advance.

答案1

得分: 0

你的切片器应按最佳实践操作日历维度表。对于实际问题,你可以将保留的度量放在IF()语句内:

IF(
ISBLANK( [Retention Analysis - Num Lapsed] ),
BLANK(),
/当前度量/
)

英文:

Just as a best practice, your slicer should be manipulating the calendar dimension table.

To this actual question though, you could put your retained measure inside an IF() statement:

IF( 
    ISBLANK( [Retention Analysis - Num Lapsed] ), 
    BLANK(), 
    /*current measure*/ 
)

huangapple
  • 本文由 发表于 2023年5月29日 22:15:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76358087.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定