topic YTD Actual vs YTD Target from tables with different granularity in DAX Commands and Tips

YTD Actual vs YTD Target from tables with different granularity

SimDam — Mon, 16 Nov 2020 15:52:10 GMT

Hi there,

I am pretty new to Power BI.

I have two Facts tables, one for the Annual Targets and one for the Actual Sales.

The Target table contains all the possible combination of Sales Office, Division, Product with Annual targets (the fiscal year is from July to June)

Sales Office	Division	Product	Target
Rome	1	Product A	100000
Rome	2	Product B	150000
Munich	1	Product A	200000
…

The Sales table contains the sales concluded every month. Note that in one month, we might sell only specific products and in another one nothing. As a convention, I have decided to use the start of the month to identify the selling month.

Sales Office	Division	Product	Sales Amount	Month
Rome	1	Product A	100	1-Oct-20
Munich	2	Product B	130	1-Oct-20
Munich	1	Product A	400	1-Nov-20
Rome	2	Product B	140	1-Nov-20

I have also a Calendar table with a relationship with the Sales[Month] and three Dim tables for Sales Office, Division and Product all connected to the two Facts Tables.

I have now to calculate the YTD Actual vs the YTD Target measures to be used in charts / pivot tables with slicers on all the dimensions month by month. So the Target should be calculated as monthly to be compared with the actual sales.

As a first step, I think I have to add a Date column in the Target table using maybe 1-Jun-21 as same date for all the rows (considering the end of our Fiscal Year is June). Then, maybe I need to calculate the monthly target from the annual etc. And finally, write the measures.

I would really appreciate your help in solving the above problem.

Thank you,

Damiano

Re: YTD Actual vs YTD Target from tables with different granularity

amitchandak — Mon, 16 Nov 2020 15:54:58 GMT

SimDam , refer to my blog on the same topic if those can help

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

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-Convert-to/ba-p/1476400

Re: YTD Actual vs YTD Target from tables with different granularity

SimDam — Tue, 17 Nov 2020 15:53:35 GMT

Hi amitchandak,

Thank you for your answer. I have already a huge target table and I would avoid introducing the day concept. The best solution for me would be to add one Date column in the table that represents the end of the FY to be connected to the Dim_Calendar and then use only measures to come up with the YTD Target at the month level.

Is it feasible?

Thank you