Measure in other Visual

[CahabaData]

I have a Trailing 12 Month measure that works when along side the current montly total, row by row in a Table Visual.

I don't think I can display that Measure in another visual, such as a line chart or bar chart - because it cannot dynamically calculate off the current montly total.  At least my experiments seem to show that.

Just thought I would sanity check as to whether there is anything I'm missing.  I was thinking a visual that is able to hold 2 values might still work if it could perform the math but am not seeing anything that allows a graphic style visual display of this measure.

[Reid_Havens]

Hi there,

Would you mind sharing what the DAX Measure it is you're using? Alternatively the awesome folks over at SQLBI cover how to write a 12 month rolling window measure here, if you're not already using this one.

[CahabaData]

greetings & thanks for reply; I am happy to share the Trailing 12 code that I used - will paste it in below.  But it may distract from the issue in that the question isn't about the code (I don't think) but about the visual.  Right now I am thinking any Dax code for a Trailing 12 that is inherently dependent on another field in the same table visual, will not work in a graphic visual (line, bar, etc) because it doesn't have the other fields needed in a row construct.

For some reason the first method would not work although I had used it before, and ended up using the second method....

Code First Method:

Sales12M = CALCULATE (

                                           [Sales],

                                              DATESBETWEEN (

                                                                           Table[ActualDate],

        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Table[ActualDate] ) ) ),

        LASTDATE (Table[ActualDate] )

                                            ))

Code Second Method:

Sales12M = CALCULATE (

                                           Sum(Sales[Value]),

                                               DATESBETWEEN (

                                                                               'Date'[Date],

                  DATEADD ( FIRSTDATE ( 'Date'[Date] ), -1; YEAR ),

                   LASTDATE ( 'Date'[Date] )

                                          )

                                             )

[Reid_Havens]

You're correct in that it becomes a bit tricky when showing on a card. One thing I've personally implimented is a DateTable that utilizes Offsets. So I have a Month/Quarter/Year offset in mine. The current Month/Quarter/Year is 0 and any future/past ones count up or down numerically depending on the time incriment (E.g. 1, 2, or -1, -2). The values update whenever the workbook refreshes in Power Query. So these can be used in either the DAX Measures or the object filters in Power BI. Happy to share with you the calendar table template I use. It is a Power Query M Query in Excel. But you can copy/paste the M code into Power BI Desktop to easily make the date table in there.

[CahabaData]

I appreciate the input.  I think the generic issue that's coming in focus for me is that depending on the visuals one will want; it has an impact as to how you data model.  Which is to say - I set up a Trailing 12 in a (Table) Visual - but probably it should instead be set up in an actual table, as part of the data model, in order to plot those same values in graphic visuals.