Year to Date calculation from Date table

[Anonymous]

Hello all,

I am fairly new to Power BI, and am a bit stuck at something.

I am creating an invoice visual from data pulled from ODBC source, and this is what it looks like as of now:

I want to add another column to this YTD visual, which is the target invoice value. So let's say the target invoice value is x for July 2022, the YTD target invoice will be 2x for August, 3x for September and so on; in the visual.

My idea for a solution was:

a) to add a column with a constant value (target amount for the year / 365) in the date table, which looks something like this: 

Please note the date table starts from 01/07/2022 till 30/06/2023.

(b) to write a YTD measure for the target value in the date table.

But I am unable to create a measure for some reason. Can someone offer an alternative solution to this?

Thanks in advance!

M

[amitchandak · Accepted solution]

@Anonymous , better to create measure using date table

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s