Adding Measure extends X-Axis...

[bvy]

See below. This project is a distillation of a greater project, so please no out of the box solutions -- such as using visual calculations, visual level filters, or the Analytics features. The solution needs to be in the DAX code. 

I have two simple tables as shown below. My 30DayAvg measure is calculating a static last 30 day average regardless of any filters selected. I was able to achieve that, although I had to use nested CALCULATE's. The problem now is, whenever I add it to the line chart, it extends the x-axis to include all dates. I want the horizontal line (purple) to appear only where there are Average Reading values (the variable blue line) -- i.e. only for the dates selected by the date slicer. 

Can someone help me with the DAX to achieve this? Thanks! 

DAX: 

30DayAvg = CALCULATE(
    CALCULATE(AVERAGE(Table1[Reading]), ALL(Dates), Table1[DateTime] >= MAX(Table1[DateTime]) - 30), 
ALLSELECTED(Table1[DateTime]))

Report: 

Data: 

[lbendlin · Accepted solution]

your data model is the wrong way round.

Dates should be the dimension table and "Table1" the facts table.

Then do what @Ashish_Mathur suggested.

Why is the dark line supposed to be straight? It's an average over 30 days.  

By the way, you can do that much simpler with Window functions.

30DayAvg = CALCULATE(AVERAGE(Table1[Reading]), WINDOW(-29,REL,0,rel,ALLSELECTED(Dates[Date])))

[v-echaithra]

Hi @bvy ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[v-echaithra]

Hi @bvy ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

[v-echaithra]

Hi @bvy ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[lbendlin]

 calculating a static last 30 day average regardless of any filters selected. I was able to achieve that, although I had to use nested CALCULATE's. The problem now is, whenever I add it to the line chart, it extends the x-axis to include all dates.

You removed all filters on the date. But it sounds like you didn't want to do that?

[bvy]

I had to do that to get the average to be correct. Do you see another way? 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[bvy]

https://www.icloud.com/iclouddrive/005zx52OtE6uGPWPbm07jWGLA#x_axis_issue

Attached. Understand that it's not always easy to upload files for those of us who work in corproate environments where everything is locked down. I thought it was straightforward enough to understand from a screenshot, but realize that a PBIX is always better when asking for help. 

Thanks for looking. 

[lbendlin · Accepted solution]

your data model is the wrong way round.

Dates should be the dimension table and "Table1" the facts table.

Then do what @Ashish_Mathur suggested.

Why is the dark line supposed to be straight? It's an average over 30 days.  

By the way, you can do that much simpler with Window functions.

30DayAvg = CALCULATE(AVERAGE(Table1[Reading]), WINDOW(-29,REL,0,rel,ALLSELECTED(Dates[Date])))

[Ashish_Mathur]

Hi,

On the X-axis, drag Date from the Dates table.

[bvy]

Thank you @Ashish_Mathur but the dark line is supposed to be constant (straight). 

[Ashish_Mathur · Accepted solution]

PBI file attached.