Rolling x months - horizintal presentation with matrix visual?

[PBIDigger]

Question: Is the matrix visual useable for x months rolling in horizontal presentation?

I think it is a common application to examine data in a rolling period of several months, e.g. often in 12 month intervals.

To realize this with a table is not a big problem, but here the representation remains necessarily vertical (in the time axis).

In graphs this is also no problem; here also horizontal representation is easy to solve, as one constructs suitable measures with time interval limitation.

With the measures used for this purpose, a matrix visual can also be fed and a horizontal table display can be realized. However, the display period cannot be limited to only the focused part (e.g. 12 months), the empty months are also displayed and you have to scroll to get to the relevant interval - very unsightly and unusable. This is due to the fact that the column label field cannot be filled with a context-sensitive measure.

I've gone through many videos and blog posts about this but they all focus only on the above mentioned unproblematic cases.

Hence my questions:
1) is there a solution with matrix visual that presents the resulting periods (e.g. the last 12 months), and only that, when the user selects a specific month?
2) is there an alternative solution, for example another (reloadable) visual (with determination of the time period at runtime by user selection, not in advance via sql, m or dax data extraction)?
3) is there (so far) no solution to this task?

I am grateful for any further or clarifying hints.

[v-kkf-msft]

Hi @PBIDigger ,

Please try the following measure:

Rolling x months = 
VAR x = 5
VAR Rolling_StartMonth =
    CALCULATE (
        MAX ( 'Calendar'[YearMonth] ),
        FILTER (
            SUMMARIZE (
                ALL ( 'Calendar' ),
                'Calendar'[YearMonth],
                "MonthRank",
                    CALCULATE (
                        DISTINCTCOUNT ( 'Calendar'[YearMonth] ),
                        FILTER ( ALL ( 'Table' ), 'Table'[Date] >= MAX ( 'Table'[Date] ) )
                    )
            ),
            [MonthRank] = x
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER ( 'Calendar', 'Calendar'[YearMonth] >= Rolling_StartMonth )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBIDigger]

Hello v-kkf-msft, thanks a lot!

This is a nice solution for a horizontal table using matrix-visual and DAX measure.
If the DAX measure would now be able to consider a user selection of the end month (filtering backwards from that to x months), then it would be the perfect solution.

To describe my problem in a different way: it must be possible for the user to set the time context to an interval, where he should only select the end point, The start point is then dependent on (well defined) x months back.

This requirement would be fulfilled by the slicer for date from calendar, with a combination of the types "between" and "relative date".
With "between" it is unfortunately necessary that the user selects both interval limits, these are completely variable (thus at the end are not necessarily exactly x months covered).
With "relative date" it always starts automatically at the last valid date from calendar (so quasi always today) and from there it covers x months backwards. I don't see any possibility to set this end date by user input with filtering.

So, the date slicer of the type "between" covers the requirement best, but is uncomfortable, because the user gets a choice regarding the start date, which he should not have.

[v-kkf-msft · Accepted solution]

Hi @PBIDigger ,

First you need to create a separate Dates table, then use the 'Dates'[YearMonth] as a slicer field and the 'Calendar'[YearMonth] as the column of the matrix.

Then use the following measure.

Rolling x months = 
VAR x = 5
VAR Rolling_StartMonth =
    CALCULATE (
        MAX ( 'Calendar'[YearMonth] ),
        FILTER (
            SUMMARIZE (
                ALL ( 'Calendar' ),
                'Calendar'[YearMonth],
                "MonthRank",
                    CALCULATE (
                        DISTINCTCOUNT ( 'Calendar'[YearMonth] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Date] >= MAX ( 'Table'[Date] )
                                && 'Table'[Date] <= MAX ( Dates[Date] )
                        )
                    )
            ),
            [MonthRank] = x
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            'Calendar',
            'Calendar'[YearMonth] >= Rolling_StartMonth
                && 'Calendar'[YearMonth] <= MAX ( Dates[YearMonth] )
        )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBIDigger]

Hello v-kkf-msft,

Thanks for your efforts - I appreciate it!

I have found that the problem obviously results from the formating of the output values within the measure. Since here values should have a different output format, these are transformed in a final step. In the course of this, the time scope seems to lose its effect.

As a workaround I had already split the matrix into two matrices (differentiated according to the format of the values). Unfortunately Power BI is not able to really fix column widths. Even with autosize-width=off, the column widths are adapted to the respective displayed numerical values (at the lates when they change). This results in a creepy appearance when the matrices have completely shifted columns to each other.

I have now capitulated and will use the slicer option 'between' after all, which unfortunately is not really an optimal solution for the report recipients. But with that I avoid all the measure and time scope issues.

An example for illustration can be found here:

https://1drv.ms/u/s!AneVdFZD0FVdcZnYOEPrdlUZHwc?e=DTL7sh

Many thanks to all who have tried to help me on the way - it was not in vain, because so I got to know other approaches.

P.S.

If someone, against all expectations, knows a solution to solve the combined time and format issue without conflict in one measure, that would of course be ingenious and gladly accepted. 😁

[amitchandak]

@PBIDigger , if you select a month and need more than that in visual, then your slicer should be on an independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[PBIDigger]

Thanks!

Yes, you are right, this is the approach to get the data of the rolling interval. And can be used to fill for example a line chart. But you can't get a horizontal(!) table with that (as far as I can see). The only approach I found is to use the matrix visual, which works in the data area correctly (only the months in the relevant interval have data).

But as its (shown) columns are determinated by the entry in the "columns" field of the visual there is no chance to skip months before or after this interval. Even if you put in the "Month" field of the calendar table or the "Month" field of the original data table - both will lead to a range of months bigger then the interval.

What can be done to restrict the horizontal expanse of the matrix resp. the presented months in it (to exactly and only the wished interval)?