Recall the sum of lastest two months available and display in Waterfall Chart

[Anonymous]

I am currently working on getting the latest two months' data and show it on Waterfall chart.
Lets say this table is named as "Game"

Month	Name	Game	Points	Goal	Mark
Jan-19	Mary	Basketball	10		x
Jan-19	Mary	Dancing	20		x
Jan-19	Samuel	Running	20		x
Jan-19	Samuel	Dancing	20		x
Jan-19	Samuel	Karake	10		x
Jan-19	Samuel	Running	5		x
Jan-19	Rikka	Dancing	15		x
Jan-19	Nicky	Karake	35		x
Feb-19	Ricky	Karake	20		x
Feb-19	Ricky	Basketball	5		x

I have the data until MARCH 2019 only. I will update data in April in beginning of May. Assume it is now end of April and I
still do not have data in April
I have appended table below "Goal" into "Game" later and so that I can put goal as target in Gauge to check if the target is reached in the month or not.

Jan-19		Dancing		300
Jan-19		Karake		200
Jan-19		Running		400
Jan-19		Basketball		500
Feb-19		Dancing		300
Feb-19		Karake		200
Feb-19		Running		400
Feb-19		Basketball		500
Mar-19		Dancing		300
Mar-19		Karake		200

...........

Jun-19		Running		400
Jun-19		Basketball		500

Given by the situation, is it possible for me to look into the Waterfall Chart of March and February only at the end of April? After I update the data in May, I would expect to have Waterfall Chart of April and March, that it will always show the latest two months with points automatically after I manually update the data.
Highly appreciate if there is any DAX solution for the question. Many thanks.

[technolog · Accepted solution]

Step 1: Identify the Latest Two Months
First, you need to create a calculated column or measure to identify the latest month and the second latest month in your data.

Measure to Identify the Latest Month
LatestMonth =
CALCULATE(
MAX(Game[Month]),
REMOVEFILTERS()
)
Measure to Identify the Second Latest Month
SecondLatestMonth =
CALCULATE(
MAX(Game[Month]),
Game[Month] < [LatestMonth],
REMOVEFILTERS()
)
Step 2: Filter Data for the Latest Two Months
Create a measure to filter the data to include only the latest two months.

IsInLatestTwoMonths =
IF(
Game[Month] = [LatestMonth] || Game[Month] = [SecondLatestMonth],
1,
0
)
Step 3: Sum Points for the Latest Two Months
Create a measure to sum the points for the latest two months.

SumPointsLatestTwoMonths =
CALCULATE(
SUM(Game[Points]),
Game[Month] = [LatestMonth] || Game[Month] = [SecondLatestMonth]
)
Step 4: Apply the Filter in the Waterfall Chart
In your Waterfall chart, use the SumPointsLatestTwoMonths measure to display the points. Additionally, use the IsInLatestTwoMonths measure as a filter to ensure only the relevant data is shown.

Step 5: Dynamic Update with Data Refresh
As your data updates, the measures will automatically recalculate to reflect the latest two months based on the latest data available.

Example DAX Code Implementation
Here's a complete example of how you can set up these measures:

Latest Month Measure
LatestMonth =
CALCULATE(
MAX(Game[Month]),
REMOVEFILTERS(Game)
)
Second Latest Month Measure
SecondLatestMonth =
CALCULATE(
MAX(Game[Month]),
Game[Month] < [LatestMonth],
REMOVEFILTERS(Game)
)
Filter for Latest Two Months
IsInLatestTwoMonths =
IF(
Game[Month] = [LatestMonth] || Game[Month] = [SecondLatestMonth],
1,
0
)
Sum Points for Latest Two Months
SumPointsLatestTwoMonths =
CALCULATE(
SUM(Game[Points]),
FILTER(
Game,
Game[Month] = [LatestMonth] || Game[Month] = [SecondLatestMonth]
)
)