Forum Discussion
Limit Matrix Displayed Dates
Hi,
I want to create a matrix that has on the rows some detaials and then on the columns some dates. I also have a slicer from which the user can select a specific date. I want the dates on the visual to be limited to 12 months relative to the selected date on the slicer. The slicer dates and the dates in the matrix come from two different tables so that there is not a relationship that could affect the filtering. In order to filter the dates I created the following measure which I apply to the visual as "AsOfDatePicker" is not blank
AsOfDatePicker =
The measure seems to work fine if I add on the visual just the columns field but it returns an empty matrix once I try to add rows as well. I tested the measure with the dates on a table visual as well and it works fine.
Here are some screenshots.
Do you have any idea on why this might be happening?
Thanks in advance.
Hi, FotFly
I demonstrated this solution to you using power bi's financial example dataset.
This could be the problem you're experiencing.
Add the end month column. To put it in the matrix columns.
Measure = Var _Maxselectdate = MAXX(ALLSELECTED('Date'[Date]),'Date'[Date]) Var _lastdate = EOMONTH(_Maxselectdate,-12) VAR _Curmonth = SELECTEDVALUE('financials'[End Month]) Return IF( _Curmonth <= _Maxselectdate && _Curmonth >= _lastdate, 1)Measure Value = Var _Maxselectdate = MAXX(ALLSELECTED('Date'[Date]),'Date'[Date]) VAR _Mindate = EOMONTH(_Maxselectdate,-1) + 1 Var _maxmonthvalue = CALCULATE( SUM(financials[Sales]), 'financials'[Date] >= _Mindate && 'financials'[Date] <= _Maxselectdate) RETURN IF(NOT ISBLANK([Measure]) || ISBLANK(SELECTEDVALUE('financials'[End Month])), IF(ISBLANK(SELECTEDVALUE('financials'[End Month])),_maxmonthvalue,SUM(financials[Sales])))Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi, FotFly
I demonstrated this solution to you using power bi's financial example dataset.
This could be the problem you're experiencing.
Add the end month column. To put it in the matrix columns.
Measure = Var _Maxselectdate = MAXX(ALLSELECTED('Date'[Date]),'Date'[Date]) Var _lastdate = EOMONTH(_Maxselectdate,-12) VAR _Curmonth = SELECTEDVALUE('financials'[End Month]) Return IF( _Curmonth <= _Maxselectdate && _Curmonth >= _lastdate, 1)Measure Value = Var _Maxselectdate = MAXX(ALLSELECTED('Date'[Date]),'Date'[Date]) VAR _Mindate = EOMONTH(_Maxselectdate,-1) + 1 Var _maxmonthvalue = CALCULATE( SUM(financials[Sales]), 'financials'[Date] >= _Mindate && 'financials'[Date] <= _Maxselectdate) RETURN IF(NOT ISBLANK([Measure]) || ISBLANK(SELECTEDVALUE('financials'[End Month])), IF(ISBLANK(SELECTEDVALUE('financials'[End Month])),_maxmonthvalue,SUM(financials[Sales])))Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much!
I figured that out by adding my filter in the measure I created for the values section instead of adding it as a filter on the visual and it worked.
Hi FotFly
It seems like the measure you've created is functioning as expected for the column fields but not when adding rows. This behavior may occur because of the context in which the measure is evaluated. When you add both rows and columns to a matrix visual, DAX may struggle to properly apply the measure to both axes.
Please use the below measure, It should work
AsOfDatePicker =
VAR SelDate = SELECTEDVALUE(AsOfCalendar[As of Date])
VAR MaxDate = MAX(Data[As of Date])
VAR MinDate = MIN(Data[As of Date])
RETURN
IF(
NOT(ISBLANK(SelDate)) &&
MaxDate <= SelDate &&
MinDate >= EOMONTH(SelDate, -12),
1,
BLANK()
)
If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.
