YTD for daily and monthly datasets

[Zosy]

Hi,

Kindly need your help to solve this problem. I have 2 sets of data, one has all daily invoices and the other one has the monthly budgets.  How could I calculate the % achieved by sales from budget if my data doesn't have the same granularity but I need to show the achievement to today's date (budget is not split by date)?

Datasets:

Invoice Number	Date	Amount
3	6-Jan-23	100
8	7-Jan-23	200
2	25-Jan-23	500
7	25-Jan-23	900
7	25-Jan-23	300
7	25-Jan-23	800
4	22-Feb-23	1300
11	15-Sep-23	500
12	20-Jan-23	2000
12	20-Jan-23	400
9	20-Mar-23	50
1	15-Sep-23	900
10	10-Apr-23	800
5	22-Feb-23	500
6	15-Sep-23	400

Budget Month	Amount
January	500
February	50
March	200
April	5000
May	300
June	800
July	200
August	100
September	5000
October	200
November	400
December	300

[v-yangliu-msft · Accepted solution]

Hi  @Zosy ,

Here are the steps you can follow：

1. Create calculated table.

Date =
CALENDAR(
    DATE(2023,1,1),
    DATE(2023,12,31))

2. Create calculated column.

Month =
MAXX(FILTER(ALL('Date'),FORMAT('Date'[Date],"mmmm")='Table2'[Budget Month]),MONTH('Date'[Date]))

3. Create measure.

Measure =
var _YTD=
SUMX(
    FILTER(ALL(Table1),
    'Table1'[Date]>=DATE(YEAR(TODAY()),1,1)&&'Table1'[Date]<=TODAY()),'Table1'[Amount])
var _month=
SUMX(
    FILTER(ALLSELECTED('Table2'),'Table2'[Month]<=MONTH(TODAY())),[Amount])
return
DIVIDE(
    _month,_YTD)

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Zosy]

Thank you! It worked 🙂