Convert yearly target to monthly target

[arnabmit]

I have a Target table with the following columns (with sample value):

ID (abc123)

Name (abc xyz)

Year (2021)

Target ($120,000)

I need a table which will give me monthly target (yearly target / 12)

ID (abc123) (abc123) (abc123) ...

Name (abc xyz) (abc xyz) (abc xyz) ...

Date (1/1/2021) (2/1/2021) (3/1/2021) ...

Target ($10,000) ($10,000) ($10,000) ...

I was thinking of creating the (yearly target / 12) value then merging a month column with the target table, but it is not working for me.

Any guidance will be greatly appreciated. Either a power query or a dax solution will do.

[v-luwang-msft · Accepted solution]

Hi @arnabmit ,

Test the below :

create a date table:

date = CALENDAR("2021,1,1","2021,12,31")

new column:

date = CALENDAR("2021,1,1","2021,12,31")

year = YEAR('date'[Date])

Then create relationship:

And create new column:

disvalue = 'Table'[Target ]/ CALCULATE(DISTINCTCOUNT('date'[month]),FILTER('date','date'[year]=MAX('Table'[Year])))

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@arnabmit , check if these two can help

In this one, you can change logic from day month

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[arnabmit]

Thanks for your reply, however I couldn't understand how to use this logic in my requirement.