How to align lines from two different date fields in a line chart

[Anonymous]

Hello Power BI Community!

I'm pretty new to Power BI and, especially, to DAX.

I have 3 tables plus a calculated table marked as date table, as shown in the picture below:

Relationships

I created a line chart showing the total_sent and total_sold_corrected values from the edition table and the forecast_value from the global_forecast table, as shown below:

Right not, the axis of the line chart is the Date from the calculated Date table. There is a slicer (Periodicidade) which allows me to select the periodicity of publications, either daily, weekly or monthly. The issue is, when forecasting, the sales of a given monthly (or weekly) publication for a certain month, the prediction_date field from the global_forecast table doesn't always coincide with the distribution_date field from the edition table. The only guarantee is that the month and year (for monthly publications) or week, month and year (for weekly publications) will coincide. In SQL I would use 

yearweek(edition.distribution_date) = yearweek(global.prediction_date)

in a WHERE clause for weekly publications and 

date_format(edtion.distribution_date,'%%Y-%%m') = date_format(global_forecast.prediction_date, '%%Y-%%m')

for monthly publications, and then use just one of the date fields to create a table. 

Is there a way to something similar in DAX? And then have the x-axis (and, consequently, the alignment date format) when I select different periodicities in the slicer. 

The intended final result would be to have something similar to this chart built with R's package dygraph:

Thank you very much and sorry for the long post. If you need sample data, please let me know.

[MFelix]

Hi @Anonymous ,

Looking at the model you have the only thing you need to change is the column you are using on the x-axis, when you place a column the values will be compared agains that column, meaning in your case you have date so you are checking for January 1st the values for both tables.

If you use the month, or the year, week the calculation will be used agains all the values in that column, going back to my example if you select January you are looking at all the values from the 1st to the 31st of January.

You can use your hierarchy to make it a drillabble chart.