Forum Discussion
Create a dynamic reference line for tracking 12 months
Hi ryan_mayu
Try below measure:
Measure = VAR CurrentDate = MAX ( 'Date-2'[Date] ) VAR PreviousDate = DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 11, 1 ) RETURN IF ( MAX ( 'Date'[Date] ) >= PreviousDate && MAX ( 'Date'[Date] ) <= CurrentDate, CALCULATE ( [Demand Charge per Train] + [Energy Charge per Train], FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] = CurrentDate ) ) )Regards,
Cherie
Super User
In this situation, I will have three tables.
1.Fact table (with month and value)
2.datetime table
3. Use DAX to create. (This will make sure the months are dynamic and you can always retrieve last month's value)
Table = VALUES(Sheet1[Month])
Then you build relationships among three tables.
Create a column in table 3 which you use DAX created to make sure the value in every month equals to the last month.
lastmonthaverage =
VAR lastmonth=MAX('Sheet1'[Month])
return CALCULATE([average],FILTER(Sheet1,Sheet1[Month]=lastmonth))
At last, you can create the chart. Drag value in table 1 to the column value and Drag the new column you created to the line value.
Microsoft EmployeeHi ryan_mayu
Try below measure:
Measure =
VAR CurrentDate =
MAX ( 'Date-2'[Date] )
VAR PreviousDate =
DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 11, 1 )
RETURN
IF (
MAX ( 'Date'[Date] ) >= PreviousDate
&& MAX ( 'Date'[Date] ) <= CurrentDate,
CALCULATE (
[Demand Charge per Train] + [Energy Charge per Train],
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] = CurrentDate )
)
)
Regards,
Cherie
- ryan_mayu
My sample data is a little bit different from yours. I want to show sales as bar chart and the value of last month I selected as a line.
If I choose Jan, Feb, Mar, we will only see three bars and the line value is 300 for all three months.
Another questions is how to create a line value which is the average of the sales I selected.
For example, if I choose Jan, Feb, Mar and Apr, we will see 4 bars and line is the average of (100+200+300+400)
If I choose 12 months in 2018, we will see 12 bars and line value automatically changes to average of (100+200+....+1200)
Thanks in advance.
- v-cherch-msft
Hi ryan_mayu
I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.
Regards,
Cherie