Forum Discussion
Line graph (Last 12 months) with field parameter.
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
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.