Forum Discussion

nanacobsbs's avatar
nanacobsbs
Helper I
3 years ago
Solved

YTD calculation

Hi.
I have a budget control graph as shown below.

The bar graph is the actual YTD and the line graph is the budget YTD.
The code is as follows.
Actual YTD = CALCULATE('Budget Control'[Actual TTL],DATESYTD('Calender'[Date]))

I would like to link this graph to a slicer so that when the user selects a single month, the YTD of the bar graph for actual is displayed up to the month selected in the slicer and not beyond.
But I want the budget YTD to be displayed through December.
How should the code for the actual YTD be calculated?

Sorry for the rudimentary question, but please help.

 

 

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi nanacobsbs ,

     

    Based on your description, I have created a simple sample:

    Please try:

    First duplicat the calendar table for slicer:

    Then apply the measrue:

    Actual = IF(MAX('Calendar'[Date])<=MAX('For slicer'[Date]),SUM('Budget Control'[Actual TTL]))

SUM of Budget = IF(MAX('Calendar'[Date])<=MAX('For slicer'[Date]),SUM('Budget Control'[Budget]))

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    nanacobsbs , Assuming Actual is a measure

    You new measure should be . else you have to use sum( [Actual TTL])

     

    Actual YTD = CALCULATE( [Actual TTL],DATESYTD('Calender'[Date]))

     

    Also the axis and filter should be on Date table

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    If you select one month and want to display more than that

     

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi nanacobsbs ,

     

    Based on your description, I have created a simple sample:

    Please try:

    First duplicat the calendar table for slicer:

    Then apply the measrue:

    Actual = IF(MAX('Calendar'[Date])<=MAX('For slicer'[Date]),SUM('Budget Control'[Actual TTL]))

SUM of Budget = IF(MAX('Calendar'[Date])<=MAX('For slicer'[Date]),SUM('Budget Control'[Budget]))

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.