Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Visualization: YTD target vs YTD actual

I've got a table with dates and sales, it's connected to CRM online which will be auto refreshed.   i've created a "Gauge" dashboard with Max (Year Target) and Target (YTD Target)   t...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

v-caliao-msft if the target table is like below, what's the DAX to get YTD target and TODAY's target.  For example, if today 30/01/2017. i would like YTD = 22+22+22+0+0+0+0=66,  and TODAY's target = 0.

 

thanks

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
9 years ago

Hi Anonymous, you could use the TOTALYTD and then as the code below is shown, it will complete the YTD until the 30 June, if you want it until December you can either change it to be "12/31" or remove it.

 

TOTALYTD([Sales],'Date'[Calendar Date],ALL('Date'[Calendar Date]),"6/30")

NOTE: For the above you do require a Date table. In which you can implement this into your model with this blog post: Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query

 

And then you can get Todays Target based on a Calculated Measure.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    GilbertQ thanks, is there any way to replace 30/06 to "today", so i don't need to update this everyday?

    • GilbertQ's avatar
      GilbertQ
      Icon for Super User rankSuper User
      9 years ago

      Hi Anonymous, you could possibly create another Calculated Measure which will can then use a filter.


      EG: 

      Todays Total = CALCULATE([My measure],Filter('Dates','Dates'[Calendar Date] = NOW()))
      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        GilbertQ what's the expression should after "totalytd"?  thanks.