Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Group Daily Target by Month and compare Weekly Sales

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.....
anandav's avatar
anandav
Icon for Skilled Sharer rankSkilled Sharer
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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.