Forum Discussion

FotFly's avatar
FotFly
Helper 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.

Anonymous's avatar
Anonymous
Not applicable
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.

FotFly's avatar
FotFly
Helper II
2 years ago

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.