Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I need help on how to get the value of the last month in a selected year in powerbi.
So I have a table that shows the month, the year and sales values and I want to create a report that will have two filters Year and Month. However, I want a situation whereby if the user select year and month filter it will show the sales value from the table but if the user select only year, it will select the value from the last month for that year.
For instance, if the table is this:
Year | Month | Sales Amount |
2019 | 2019 October | 1000 |
2019 | 2019 November | 900 |
2019 | 2019 December | 1400 |
2020 | 2020 January | 1200 |
2020 | 2020 February | 980 |
If the user select 2019 in year and select All for month, the report should show
Year Sales
2019 1400 (which is the values for the last month in the selected year)
if they select 2019 in year and Novermber in Month
Year Sales
2019 900 (which is the value for the selected month and year)
Thanks in advance for the help.
Solved! Go to Solution.
@Anonymous , if you date or create a date like
Date = "01-"&[Month] & "-" [Year]
Try a measure like
lastnonblankvalue(Table[date],sum(Table[Sales Amount]))
Hi @Anonymous
I build a table like this to have a test.
Build a calender table to build slicer.
Slicer = CALENDARAUTO()
Year = YEAR(Slicer[Date])
Month = MONTH(Slicer[Date])
Result:
Build two measures to achieve your goal.
M_Year =
IF(ISFILTERED(Slicer[Year]),CALCULATE(MAX('Table'[Year]),FILTER(ALL('Table'),'Table'[Year]=MAX(Slicer[Year]))),BLANK())
Sale Amount =
VAR _MonthAmount =
CALCULATE (
SUM ( 'Table'[Sales Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[Month].[MonthNo] = MAX ( Slicer[Month] )
&& 'Table'[Year] = MAX ( Slicer[Year] )
)
)
VAR _LastAmount =
CALCULATE (
SUM ( 'Table'[Sales Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[Month]
= CALCULATE (
LASTDATE ( 'Table'[Month] ),
FILTER ( ALL ( 'Table' ), 'Table'[Year] = MAX ( Slicer[Year] ) )
)
)
)
RETURN
IF (
ISFILTERED ( Slicer[Year] ),
IF ( ISFILTERED ( Slicer[Month] ), _MonthAmount, _LastAmount ),
BLANK ()
)
Result:
Only select Year 2019:
Select Year 2019 and Month 11:
You can download the pbix file from this link: How to get Max Value for a selected month and year
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Hi @Anonymous
I build a table like this to have a test.
Build a calender table to build slicer.
Slicer = CALENDARAUTO()
Year = YEAR(Slicer[Date])
Month = MONTH(Slicer[Date])
Result:
Build two measures to achieve your goal.
M_Year =
IF(ISFILTERED(Slicer[Year]),CALCULATE(MAX('Table'[Year]),FILTER(ALL('Table'),'Table'[Year]=MAX(Slicer[Year]))),BLANK())
Sale Amount =
VAR _MonthAmount =
CALCULATE (
SUM ( 'Table'[Sales Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[Month].[MonthNo] = MAX ( Slicer[Month] )
&& 'Table'[Year] = MAX ( Slicer[Year] )
)
)
VAR _LastAmount =
CALCULATE (
SUM ( 'Table'[Sales Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[Month]
= CALCULATE (
LASTDATE ( 'Table'[Month] ),
FILTER ( ALL ( 'Table' ), 'Table'[Year] = MAX ( Slicer[Year] ) )
)
)
)
RETURN
IF (
ISFILTERED ( Slicer[Year] ),
IF ( ISFILTERED ( Slicer[Month] ), _MonthAmount, _LastAmount ),
BLANK ()
)
Result:
Only select Year 2019:
Select Year 2019 and Month 11:
You can download the pbix file from this link: How to get Max Value for a selected month and year
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , if you date or create a date like
Date = "01-"&[Month] & "-" [Year]
Try a measure like
lastnonblankvalue(Table[date],sum(Table[Sales Amount]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |