Target Setting vs last year

[Arif270182]

Good Morning 

I am looking for help in setting target number based on period filter with value of last year, below is my scenerio 

I select date from 1st sep to 30th sep 

i got 2 values day by day - current year sales and last year sales

lets say last year total sep is 10,000 & 

current year so for MTD(19 days) is 6,000

now my target is 4,000 for next 11 days

i want daynamic formula how this could be fixed in per day.

also i would like to clear based on my period selected the value should be changing.

thanks in advance.

[Anonymous]

Not sure about data model. Assuming a usual data model, somthing like this below will help you.

CALCULATE( SUM(Sales_Fact[Sales]),SAMEPERIODLASTYEAR('Date'[Date])) - SUM(Sales_Fact[Sales])

Share some sample data, details about your model for more specific solution.

Thanks

Raj 

[Arif270182]

Thats the table as you can see negative value is what i need to break in rest of the days 

[Anonymous]

You already have current and last year sales. so, simple SUM( sales[CYSales]) - SUM( Sales[LYSales]) should give you the expected result.

Thanks

Raj

[Arif270182]

yeah i have already that value which is Sales Difference my point is i want to break that 2,294,091 divide by rest of days  based on filtered date. 

[Anonymous]

Do you want to divide the difference by the remaining days in sep and display under CY sales column? can you please explain... also can you put the expected result in table format for better understanding?

Thanks

Raj

[Arif270182]

Hi Rajendra 

as you see in the picture which i have shared earlier it shows negative value which is difference what i am looking is that based on this value which is my target i want a formula/calculation which shows me based on the remaining days here we have period of september so 11 days 

so today the table shows like this

Current year :- 3,214,649

Difference :- 1,931,687

now remaining days for sept is 10 days 

logical answer is 1,931,687/10 = 193,687 - i want this value to be daynamic.

i hope its clear now 

[Anonymous]

Hi @Arif270182

Can you please share the data and the pbix file you have so that a solution can be attempted.

Cheers

CheenuSing

[Arif270182]

Hi CheenuSing

Thank you - as i upload you pic of what i am excatly looking for let me breif you 

in terms of sales we have comparision to last year so current year MTD vs complete month last year, so as you can see in pic i have negative (sales difference) i want to divide this value with remaining days of month so here it would be 9, so basically it would be 1931687/9 days ..... hence my requirement is i want this calculation automated from calculation/ formulation/ ? or i don't know how it can be .. i hope i have explained u 

 Difference table

[Ashish_Mathur]

Hi,

Assuming Sales Diff is a measure, write this new measure

Measure=-[Sales Diff]/(EOMONTH(TODAY(),0)-TODAY())+1

Hope this helps.

[Arif270182]

hi  Ashish_Mathur

Appreciate your response yes the formula is working but the value is not coming accurate here is practical screen so if u notice my sales difference is -1,696,334 & remaining days to close month is 9 so based on this calculation my target per day should be 188,481 but the value what i am getting is 212,043 i used the same formula which you mentioned in your post 

Target per day = -[Sales Diff]/(EOMONTH(TODAY(),0)-TODAY())+1

[Ashish_Mathur · Accepted solution]

Hi,

I think a bracket is missing.  Try this

=-[Sales Diff]/((EOMONTH(TODAY(),0)-TODAY())+1)

[Arif270182]

Thank you Ashish_Mathur its working perfectly 

Just another que incase if i want to add period to quarter - or another addidional month then would the formula change  ? 

[Ashish_Mathur]

Hi,

As long as the "Sales Diff" measure has been written to take into account the new quarters and months, my formula will work absolutely fine.

[Arif270182]

hi Ashish_Mathur

appreciate your responses and feedbacks 

Could you please share / calculate formula how it would be also

i wish to have another update on same report , i am adding weekdays as filter below is snap 

so i would like to check if here incase i select any day then can i get a spefic target like below 

so in above pic we can see i have selected Wed so logically there is only 1 wednesday in this period so my "target per day" should have been identical to "sales diff" i.e. 134,483 instead its showing 19,212 ( i have no idea from where its coming).

is there possibilty can we solve or calculate it dynamically ?

appreciate your responses.

[Ashish_Mathur]

Hi,

Your question is not clear.

[Arif270182]

Hello 

Apologies if it wasn't clear 

what i mean now that we got accurate value for "target per day" i am looking ahead to how i could break this number more accurate based weekdays

so based on snaps shared in previous message we see our "target per day" is 197,991 (thats for the rest of the days to close september - which are empty cells of table) i hope this is clear.

now here is what i am looking for so 24th sep is Monday, 25th Tues, 26th Wed and so on, 

so incase if i select/filter monday then based on remaining monday it shall give me "target per day" only for monday so like in the snap i had shared i have selected Wednesday so you can notice there is one empty cell in table i.e. 26th september so if you notice the value of "target per day" is showing 19,212 which should not be correct logically.

As per my understanding it should give me value of sales difference in this case coz we have only 1 wednesday.

i hope i have ellobrated it clear enough to make you understand if not then let me know if i could reach you in other mode of communication.

thank you 

[Ashish_Mathur]

Hi,

Try this measure

=CALCULATE(-[Sales Diff]/((EOMONTH(TODAY(),0)-TODAY())+1),ALL(Calendar[Weekday])

In the Calendar Table, there should be a weekday column.  The weekday slicer should be created from the Weekday column of the Calendar Table.

[Arif270182]

Hi Ashish 

thank you so much for extending your support but seems the formula isn't working as i have snaps for you below, i have table for CY Date where my weekdays column is created based on dates. please let me understand if had it written correct the formula ?

[Ashish_Mathur]

Hi,

I will need to look at your PBI file.  Share the link from where i can download your PBI file.

[Arif270182]

Hi Ashish_Mathur

Appreciate your support, i hope this would help you to get our solution. 

https://1drv.ms/u/s!An1F3GPoODE4afZbGK_7kvcBQN8

Thank you