Group Daily Target by Month and compare Weekly Sales

[Anonymous]

Hi,

This seems like it should be straight forward however I am at a bit of a loss.

I have three tables. A Sales table, a Date table and a Target table.

NOTE: date = 1/1/2017, 1/2/2017, etc...

My sales is split by date/region/store/order ID/Product ID/etc.
My targets are split by date/region/store.

My date table has the date period and every view of date that I need, Month/Year/MonthYear/Week/etc.

I have scoured forums and tried quite a few solutions to no avail and I feel I am missing a basic concept or formula with DAX.

Essentially my table is as below:

Week	Month	Sales	Target	Achieved%
1	1	100	110	90.9%
2	1	467	500	93.4%
3	1	254	230	110.4%
4	1	125	120	104.2%
5	2	155	150	103.3%
6	2	235	250	94.0%
7	2	152	250	60.8%
8	2	192	200	96.0%
9	3	585	550	106.4%
10	3	867	500	173.4%
11	3	931	700	133.0%
12	3	343	600	57.2%

And I want it to look like this

Week	Month	Sales	Target	Contribution %
1	1	100	960	10.4%
2	1	467	960	48.6%
3	1	254	960	26.5%
4	1	125	960	13.0%
5	2	155	850	18.2%
6	2	235	850	27.6%
7	2	152	850	17.9%
8	2	192	850	22.6%
9	3	585	2350	24.9%
10	3	867	2350	36.9%
11	3	931	2350	39.6%
12	3	343	2350	14.6%

I am not sure if anyone will be able to help me understand a level further in addition to this, however as a Qlik user - I would be able to aggregate any expression and aggregate as required by using AGGREGATE,
e.g. Target_byMonth = AGGREGATE(SUM(Target),[Month Number]) #this gives me a virtual table of summed target by month no..
Then I could simply compare write SUM(Sales) / Target_byMonth and it would return the second table as above.
What is the equivalent in DAX so that I can aggregate my measures by any dimension I want?

Thanks and much appreciated for help!

[anandav]

@Anonymous,

Try below measures.

Target Cummulative by Month =
CALCULATE(
    SUM(Table1[Target]),
    FILTER(ALLEXCEPT(Table1, Table1[Month]), Table1[Week] <= Table1[Week])
)

New Percentage =
MIN(Table1[Sales]) / [Target Cummulative by Month]

(Format the above as Percentage)

Hope this helps.

If this solves your problem please mark as solution.

[Anonymous]

@anandav

Thanks for replying. It works in the table format using dates from the target table. However I need to use the Calendar table and in a line chart that displays by week (not month), however these are what I'm finding. Could this be an issue with my data model?

I have the Target table linked to my Calendar table (by date which works) and the target table linked to my 'Master Store Table' and both the Calendar and Master Store table are related to my data model? However note my targets work when summing the day without creating the measure you have as below which makes me think it's not the model but the calc? See below for reference.