Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
As a beginner, I searched lots of articles, and it worked out with showing the visual data for tracking 12 months.
Now there is another problem, which is, I would like to use the last-month average value as the 12-month reference line.
Please help me.
This is what I expect.
【Original Files】
Solved! Go to Solution.
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
Thank all of your replies.
I made a little mistake about what I want. Please allow me to explain again.
The average line is a value of the last month.
The formula is as below.
[Trailing 12-month accumulative Energy Cost] / [Trailing 12-month Trains]
Take "Nov-2018" as example, the average value should be 28,016 dollars.
Originally, I tried to use LOOKUPVALUE, but it cannot be shown only between 2017-12 to 2018-11.
Anyway, I revised the pbix file. I really need your hlep indeed.
Thank you.
Hi,
Have you tried my solution?
Hi @julie_tai
Try below measure:
Measure = VAR CurrentDate = MAX ( 'Date(Irrelevant)'[Date] ) VAR PreviousDate = DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 11, 1 ) RETURN IF ( MAX ( 'Date'[Date] ) >= PreviousDate && MAX ( 'Date'[Date] ) <= CurrentDate, CALCULATE ( [TTM Average Energy Cost per Train], FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] = CurrentDate ) ) )
Regards,
Cherie
Hi,
You may download my PBI solution file from here.
Hope this helps.
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.
Proud to be a Super User!
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
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.
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |