Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have two visuals:-
1) MatrixTable has a column called Budget
2) Another chart (Date) shows the Budget by Year, Quarter and Month Number (1, 2, 3....12)
I would like to add a column to MatrixTable that shows the Budget up to the month selected in the Date chart, and if no month is selected in the chart, the Budget has to show data up to the latest month.
How do i do this using a measure ?
So ChatGPT came up with the below DAX
Budget Up to Selected Month =
VAR SelectedMonth = MAX('Date'[Month Number])
VAR LatestMonth = MAX('Date'[Month Number])
VAR FilteredBudget =
CALCULATE(
SUM('MatrixTable'[Budget]),
FILTER(
ALL('Date'),
'Date'[Month Number] <= SelectedMonth || ISBLANK(SelectedMonth)
)
)
RETURN
IF(ISBLANK(FilteredBudget), BLANK(), CALCULATE(SUM('MatrixTable'[Budget]), 'Date'[Month Number] = LatestMonth))
The code above works only when i select an individual specific month, but not multiple month selections or even without any selection. Eg. below...
Anyone has ideas how to solve this please ? Much appreciated ! Thanks !
It wasn't the solution that i was looking for unfortunately. I managed to solve the issue eventually using another method. Thanks.
Hi @DanteL ,
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 if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DanteL ,
The Above DAX that you have given is using a filter with "=" operator. So it will always give the data for the month selected.
I would write a DAX like the one below
Sales =
VAR MAXMONTH = SELECTEDVALUE('Date'[MonthNo])
RETURN
IF(NOT(ISFILTERED('Date'[MonthNo])), SUM(tblInternetSales[SalesAmount]),
CALCULATE(SUM(tblInternetSales[SalesAmount]), 'Date'[MonthNo] <= MAXMONTH))
Note: When using the above measure in your visual, you should not use the Date Column in the visual
The Above DAX works well both when a Month is selected and when Nothing is selected. But it won't work when Multiple Months are selected. When we are getting data until a particular month, multi selection might not make sense. Hence sharing this DAX with you
The Screenshot of the Output is given below for your reference
When one month is selected
When Nothing is selected