Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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]
)
)
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]
)
)
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
10 |