Forum Discussion

cbuckne1's avatar
cbuckne1
Regular Visitor
6 years ago
Solved

Budget vs actuals Combo Visual

Im trying to create a visual that will show cumulative actuals by month. I want it to look like the below but instead of each month showing the cumulative total, I want to show the actuals for that m...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    cbuckne1 ,

     

    In calculate column, the expression should be as below:

    Chart Value = CALCULATE(SUM('Table'[Actuals]), FILTER('Table', 'Table'[Index] <= EARLIER('Table'[Index])))

     

    In measure, the expression should be as below:

    Chart Value Measure = CALCULATE(SUM('Table'[Actuals]), FILTER(ALL('Table'), COUNTROWS(FILTER('Table', 'Table'[Index] >= EARLIER('Table'[Index])))))

     

     

    Community Support Team _ Jimmy Tao

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

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Ensure you have a Calendar Table with Year and Month columns.  Create a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag Year and Month from the Calendar Table.  Write these measures

    Total sales = SUM(Data[Sales])

    Cumulative sales = CALCULATE([Total sales],DATESYTD(Calendar[Date],"31/12"))

    Hope this helps.

vivran22's avatar
vivran22
Icon for Community Champion rankCommunity Champion
6 years ago

Hello cbuckne1 

 

Do you need a running total as and when the month progresses or just total of current month & previous month?

 

In case if it is later, then you may use following measure in your visual:

 

Prev Month Actual =
 CALCULATE(
            SUM(dtTable[Actual]),
            PREVIOUSMONTH(dtTable[Month])
            )
            +SUM(dtTable[Actual])

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

cbuckne1's avatar
cbuckne1
Regular Visitor
6 years ago

Im looking for a running total.

 

In the most simplistic of terms the below is what Im looking for.

 

 

 

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    cbuckne1 ,

     

    In calculate column, the expression should be as below:

    Chart Value = CALCULATE(SUM('Table'[Actuals]), FILTER('Table', 'Table'[Index] <= EARLIER('Table'[Index])))

     

    In measure, the expression should be as below:

    Chart Value Measure = CALCULATE(SUM('Table'[Actuals]), FILTER(ALL('Table'), COUNTROWS(FILTER('Table', 'Table'[Index] >= EARLIER('Table'[Index])))))

     

     

    Community Support Team _ Jimmy Tao

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

     

     

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

    Hi,

    Ensure you have a Calendar Table with Year and Month columns.  Create a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag Year and Month from the Calendar Table.  Write these measures

    Total sales = SUM(Data[Sales])

    Cumulative sales = CALCULATE([Total sales],DATESYTD(Calendar[Date],"31/12"))

    Hope this helps.