Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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

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 NameGamePointsGoalMark
Jan-19MaryBasketball10 x
Jan-19MaryDancing20 x
Jan-19SamuelRunning20 x
Jan-19SamuelDancing20 x
Jan-19SamuelKarake10 x
Jan-19SamuelRunning5 x
Jan-19RikkaDancing15 x
Jan-19NickyKarake35 x
Feb-19RickyKarake20 x
Feb-19RickyBasketball5 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.

1 ACCEPTED SOLUTION
technolog
Super User
Super User

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]
)
)

View solution in original post

1 REPLY 1
technolog
Super User
Super User

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]
)
)

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors