Target by year vs transactions by day

[Ritaf]

Hi,

I have an yearly targets at table which related to transactions at another table and need to calculate the progress of execution.

Our DBA recpmmended me to put the targets by date format , for example a target of 2020 as 01/01/2020. (both of tables related to calendar table by date field). The poblems are coming when i'm trying to calculate  a execution. It shows me a result of only the date i put as a "target day" . What dax code i need to use to make it work for all sellected period and referense a target as an yearly?

[amitchandak]

@Ritaf , I have a video for this. But the way Power BI functions work, you have to move it on the last date of the year.

https://www.youtube.com/watch?v=yPQ9UV37LOU

https://youtu.be/6lzYOXI5wfo

File location: https://community.powerbi.com/t5/Quick-Measures-Gallery/Allocating-Targets-in-Power-BI-Why-You-misse...

In the second video I use firstnonblankvalue , to get it from ist date

[Ritaf]

Thank you! This is my case. 

I changed the target date for end of year , and started to create 

CLOSINGBALANCEYEAR('Mrasures'[sumPurchaseSupplierLevel],dimDatetime[DateKey]) an it's blank....

my sum is not "standart" because i have an summarize inside it by category...

the formula i used for calculating sum purchase is

sumPurchaseSupplierLevel = sumx(SUMMARIZE('factSupplierManufactorRebate',

factSupplierManufactorRebate[מפיץ],"sumParchase",sum(factOrdersPurchase[סה"כ עלות,$])),[sumParchase])

may be it causes the problem...

Thanks a lot for all your help and assistance

[amitchandak]

@Ritaf ,Can you share sample data and sample output in table format? 

[v-lili6-msft]

HI  @Ritaf 

For your case, I would suggest you add a Target by year table, for example:

Year	Target
2019	10000
2020	20000

Then create a relatinship with fact table by year column, now use this target value from this table.

Regards,

Lin

[Greg_Deckler]

@Ritaf - I'm not sure I 100% understand what you are asking. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

That said, if you have dates for goals, you could add columns like:

Year = YEAR('Table'[Date])

Then a 1/1/2020 goal would have a year of 2020 and a 1/1/2021 goal would be 2021 and you could use a slicer or something to pick them. Or, if you have a date table with sales, you could also have a year column and maybe you put a distinct year table in between the two relate everything on Year columns and away you go.

Again, not sure I am clear on what you are going for, so just spit-balling here.

[Ritaf]

Hi , thank you for responding. Unfortunatly i don't think it so simple. The report should be flexible , if i make all my referrences by year , it will be confuse to show a monthly progress for example...and yes i.have a dimdate table related to goals and to transactions ...