Calculate price per month and show it on line chart visual

[smatesic]

Hello,

I have PBI report with sample data. The report is really simple - it has two tables and calculated DimTable. In one table I have columns shown on the picture belove:

In another table I have name of the buyer, which Package/Tarifa he's buying and when his Package is expiring - DueDate column. DueDate is connected to Date in DimTable. 

Question is - can I show in Line chart or in Column chart visuals PricePerMonth values by Month or YearMonth for every month?

So that column bar writes $8,33 for January, February and so on. Now it looks like this:

Is there a way to make thate calculated column which will calculate PricePerMonth for every month?

Thanks

[v-yuezhe-msft · Accepted solution]

Hi @smatesic,

Firstly, create a new column named “Start Date” in your second table using the following formula:

Start Date = DATE(YEAR(Table2[DueDate]),MONTH(Table2[DueDate])-RELATED(Table1[DurationMonths]),DAY(Table2[DueDate]))

Secondly, create a new table by joining your second table and DimTable with the following formula:

Table = FILTER(CROSSJOIN(Table2,VALUES(DateDimension[Date])),DateDimension[Date]>=Table2[Start Date]&&DateDimension[Date]<=Table2[DueDate])

Thirdly, drag the date column of the newly created table to Axis of column chart, which looks like below, the visual calculates PricePerMonth for every month. For more details, please review the example in this attached PBIX file.


In addition, from my point of view, it makes sense when you create relationship between the newly created table and the second table, then drag the date coulmn to Axis of column chart. The visual will display as follows.


Thanks,

Lydia Zhang

[smatesic]

Thank you, this model works. I needed to see what would price look like in next 2 year period so I adapted formula and now it looks like this

Forcasting = FILTER(CROSSJOIN(Klijenti; VALUES(DimTable[Date])); DimTable[Date] >= Klijenti[Start Date] && DimTable[Date] <= Klijenti[DateIn2Years])

Thank you for your help