英文:
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 -
- A Calendar table, the date column of which is used to create the x-axis of a bar graph
- 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 -
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 -
- A Calendar table, the date column of which is used to create the x-axis of a bar graph
- 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 -
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*/
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论