Forum Discussion

FotFly's avatar
FotFly
Icon for Helper II rankHelper II
2 years ago
Solved

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 vi...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Ray_Minds's avatar
Ray_Minds
Icon for Solution Supplier rankSolution Supplier
2 years ago

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.