Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Straight Line Towards Monthly Goal

Hi all, 

I am trying to create a graph that compares actuals vs monthly goals.  The actuals are recorded as individual events with dates occuring throughout the month.  The goals are on a seperate table which only provides one value for the overall monthly goal, the goal is the same, regarless of the month.  I am specifically having an createing a line that starts at 0 on day 1 of the month and continues in a straight line towards the monthly goal on the final day of the month. 

Here's a sketch of the graph that I am working to create: 

 The data is coming from 2 tables and a reference calendar I created in PowerBi

The first is sales on the day they occur (they can have multiple sales in one month)

 

The second is a table with a monthly goal:

 This is joined to the first table according to the name. 

 

I also created a calendar table

Calendar_table = CALENDAR (DATE (2023, 10, 1), DATE (2024, 12, 31)), which is joined to the actual sales table. 
 
 
Below is what I have attempted so far.  If you already have an answer, no need to read what is below:
I have been able to create values for the first and last date of the month. 
I created this calculation that sets the first day to 0.
IF( Calendar_table[Date]=EOMONTH(Calendar_table[Date],-1)+1, 0)
 
I am working to create a measure that only returns the monthly goal, such as. 
IF( Calendar_table[Date]=EOMONTH(Calendar_table[Date]), MonthlyGoalTable([goal]))
 
And then my thought is that I can create a third calculation that would check the first measure and return 0 on the first day and then return the goal on the final day of the month. 
if(isblank([calc that returns 0 on the first day of the month]),[calc that returns the goal at the end of the month])
 
tl:dr:  Trying to create a line that starts at 0 and ends at the monthly goal.  The monthly goal is on a seperate table and only has single value. 
 
If anyone has any suggestions, they would be greatly appreciated!
Thanks, 
CJ
 
Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Your line formula needs to be 

     

    DIVIDE([current day of month],[number of days in month],0) * [Monthly Goal]

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Is it fair to assume that there will be a Year column in the Monthly goal table.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Unfortunatley no.  There is just a single number under MonthlyGoal.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Very close to what I need. Unfortunately, I need it to be filterable according to Individual.  Currently, the line stays the same regardless of whether a filter is applied. I trying LOOKUPVALUE, but using [individual] for search_value was not working.  Thanks!