Line graph (Last 12 months) with field parameter.

[Jonathan_Richar]

112

Hey guys!
I hope they are well.

I would like to request your help with a DAX measurement. I tried looking online for something similar to what I need to do and I couldn't find it.

I have a field parameter that returns the name of the month or quarter depending on the selection in the filter (image 1), I need a DAX measure that returns the last 12 months, I tried several times, but I couldn't.

In image 2 it is the closest I could do, but ideally the x-axis should change to month or quarter according to the selection made in the 1st image.

Thank you for all responses.

[amitchandak]

@Jonathan_Richar , In case you select a date/month or qtr and still need 12 month on slicer, then you need one disconnected table

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

//DateInd is joined with inactive join, Date is joined with Table with Active join. and Date table is used in slicer , and Dateind is used on axis

new measure =
Var _max = Max('Date'[Date])
var _min = EOMONTH(_max, -12)+1
return
CALCULATE([Net Sales Measure],filter(ALL('Date'), 'Date'[Date]>=_min && 'Date'[Date]<=_max) ,USERELATIONSHIP('DateInd'[Date], Sales[Sales Date]))

or

new measure =
Var _max = Max('Date'[Date])
var _min = EOMONTH(_max, -12)+1
return
CALCULATE(sumx(Values(Date[Date]), [Net Sales Measure]) ,filter(ALL('Date'), 'Date'[Date]>=_min && 'Date'[Date]<=_max) ,USERELATIONSHIP('DateInd'[Date], Sales[Sales Date]))

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

[Jonathan_Richar]

@amitchandak

Firstly, thank you very much for the clarification.

One question, how would it be possible for the x axis of the graph to be variable according to the type of period (Quarter or Month) chosen?

For example: If you select "month" the graph would show months (Jan, Feb...) if you select "Quarter" the graph would show (Q1, Q2....)

If possible, could you provide me with the file (.pbix) you used for the answer.

Thank you in advance for your time and knowledge.