Forum Discussion

gco's avatar
gco
Resolver II
6 years ago
Solved

PowerKPI aggregation

  Hi there,   I am trying to find a way to display the aggregated YTD Total Originations and YTD Budget on the PowerKPI.  How i got it setup is only displaying the max date that is selected on the...
Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    Hi gco ,

    I want to know which formula you use to calculate YTD. The following formula can calculate YTD:

    [YTD Sales] = CALCULATE ( [Sales Amount], DATESYTD( 'Date'[Date] ) )
    [YTD Sales] = TOTALYTD ( [Sales Amount] , 'Date'[Date] )
    [YTD Sales] =
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date'[Date] ),
        AND (
            'Date'[Date] >= DATE ( 2019, 1, 1 ),
            'Date'[Date] <= MAX ( 'Date'[Date] )
        )
    )
)

    Maybe you can try the third formula to see if it works, if it doesn't work, kindly share your sample data and expected result if you don't have any Confidential Information.

    Best Regards,
    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

gco's avatar
gco
Resolver II
6 years ago

The screenshot does not seem to show 

 

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    Hi gco ,

    I want to know which formula you use to calculate YTD. The following formula can calculate YTD:

    [YTD Sales] = CALCULATE ( [Sales Amount], DATESYTD( 'Date'[Date] ) )
    [YTD Sales] = TOTALYTD ( [Sales Amount] , 'Date'[Date] )
    [YTD Sales] =
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date'[Date] ),
        AND (
            'Date'[Date] >= DATE ( 2019, 1, 1 ),
            'Date'[Date] <= MAX ( 'Date'[Date] )
        )
    )
)

    Maybe you can try the third formula to see if it works, if it doesn't work, kindly share your sample data and expected result if you don't have any Confidential Information.

    Best Regards,
    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • gco's avatar
      gco
      Resolver II
      6 years ago

      Hi v-joesh-msft ,

       

      Your solution was spot on!  I had to modify the first date filter to 'Date'[Date] >= FIRSTDATE('Date'[Date]), so it is flexible enough that it changes automatically for each year.  But overall, your 3rd measure works.

       

      'Date'[Date] >= DATE ( 2019, 1, 1 )

       

      With the new measure, i am now able to select  multiple dates and Power Matrix gives me the aggregated total!

      Thank you again, you deserve a medal for helping me!