Cumulative total

Hi

I have uploaded the file to

https://www.dropbox.com/s/kgojyt35s0i97d1/test.pbix?dl=0

Below you can see the datamodel and how I have used this in the dashboard

The green bars is Percentage actuals (=Actuals/Budget). Budget is mapped to 01/01/20xx so when I want to calculate a quarterly % Actuals, I have to remove the filter on Quarter, else the budget will be 0 in Q2, Q3 and Q4

Percentage actuals = 
DIVIDE(
    SUM(Measuretable[Actuals]);
    CALCULATE(SUM(Measuretable[Budget]);ALL(Measuretable[Quarter]))
)

Percentage actuals cumulative from Qx is the same measure, but now I want it to be cumulative. The special requirement is:

• that this measure can only be shown in the chart for the quarters after the selected quarter: this is what i did with the orange code below and with a disconnected table but maybe this can be improved? It is however amazing because Quarter is a textfield which contains Q1, Q2, Q3 and Q4. I don't even know how DAX knows how Q3 is greater than Q2 because it is text :) but it works!
• that this measure can only cumulate the "Percentage actuals"for the quarters after the selected quarter. I didn't succeed to make this happen :s

Percentage actuals cumulative from Qx = 
DIVIDE(
    IF(SELECTEDVALUE(Measuretable[Quarter])>SELECTEDVALUE(Disconnectedtable[Quarter]);
        CALCULATE(
            SUM(Measuretable[Actuals]);
            FILTER(ALL(Measuretable[Quarter]);Measuretable[Quarter]<=MAX(Measuretable[Quarter])))
    );
    CALCULATE(
        SUM(Measuretable[Budget]);
        ALL(Measuretable[Quarter])
     )
 )

In the chart below the endresult should be instead:

• dark column Q3 = 22,2%
• dark column Q4 = 22,2% + 30,5% = 52,7%

The business question is actually something different. I need this technique to calculate a forecast. It is however pragmatic but maybe interesting to share :)

• Forecast for Q3 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q3
• Forecast for Q4 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q4

What this will actual say is: when we use as much budget in Q3 and Q4 as previous year, we will go over budget (more than 100% in Q4) or we will still have enough budget.

I hope this info can help to gain insight in the problem. I am happy to provide more info if I still didn't make this clear enough!

Hi

I have uploaded the file to:

https://www.dropbox.com/s/kgojyt35s0i97d1/test.pbix?dl=0

Below you can see the datamodel and how I have used this in the dashboard

The green bars is Percentage actuals (=Actuals/Budget). Budget is mapped to 01/01/20xx so when I want to calculate a quarterly % Actuals, I have to remove the filter on Quarter, else the budget will be 0 in Q2, Q3 and Q4

Percentage actuals = 
DIVIDE(
    SUM(Measuretable[Actuals]);
    CALCULATE(SUM(Measuretable[Budget]);ALL(Measuretable[Quarter]))
)

Percentage actuals cumulative from Qx is the same measure, but now I want it to be cumulative. The special requirement is:

• that this measure can only be shown in the chart for the quarters after the selected quarter: this is what i did with the orange code below and with a disconnected table but maybe this can be improved? It is however amazing because Quarter is a textfield which contains Q1, Q2, Q3 and Q4. I don't even know how DAX knows how Q3 is greater than Q2 because it is text :) but it works!
• that this measure can only cumulate the "Percentage actuals"for the quarters after the selected quarter. I didn't succeed to make this happen :s

Percentage actuals cumulative from Qx = 
DIVIDE(
    IF(SELECTEDVALUE(Measuretable[Quarter])>SELECTEDVALUE(Disconnectedtable[Quarter]);
        CALCULATE(
            SUM(Measuretable[Actuals]);
            FILTER(ALL(Measuretable[Quarter]);Measuretable[Quarter]<=MAX(Measuretable[Quarter])))
    );
    CALCULATE(
        SUM(Measuretable[Budget]);
        ALL(Measuretable[Quarter])
     )
 )

In the chart below the endresult should be instead:

• dark column Q3 = 22,2%
• dark column Q4 = 22,2% + 30,5% = 52,7%

The business question is actually something different. I need this technique to calculate a forecast. It is however pragmatic but maybe interesting to share :)

• Forecast for Q3 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q3
• Forecast for Q4 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q4

What this will actual say is: when we use as much budget in Q3 and Q4 as previous year, we will go over budget (more than 100% in Q4) or we will still have enough budget.

I hope this info can help to gain insight in the problem. I am happy to provide more info if necessary!

Friendly greets

Hi

I have uploaded the file to

https://www.dropbox.com/s/kgojyt35s0i97d1/test.pbix?dl=0

Below you can see the datamodel and how I have used this in the dashboard

The green bars is Percentage actuals (=Actuals/Budget). Budget is mapped to 01/01/20xx so when I want to calculate a quarterly % Actuals, I have to remove the filter on Quarter, else the budget will be 0 in Q2, Q3 and Q4

Percentage actuals =
DIVIDE(
    SUM(Measuretable[Actuals]);
    CALCULATE(SUM(Measuretable[Budget]);ALL(Measuretable[Quarter]))
)

Percentage actuals cumulative from Qx is the same measure, but now I want it to be cumulative. The special requirement is:

• that this measure can only be shown in the chart for the quarters after the selected quarter: this is what i did with the orange code below and with a disconnected table but maybe this can be improved? It is however amazing because Quarter is a textfield which contains Q1, Q2, Q3 and Q4. I don't even know how DAX knows how Q3 is greater than Q2 because it is text :) but it works!
• that this measure can only cumulate the "Percentage actuals"for the quarters after the selected quarter. I didn't succeed to make this happen :s

Percentage actuals cumulative from Qx = 
DIVIDE(
    IF(SELECTEDVALUE(Measuretable[Quarter])>SELECTEDVALUE(Disconnectedtable[Quarter]);
        CALCULATE(
            SUM(Measuretable[Actuals]);
            FILTER(ALL(Measuretable[Quarter]);Measuretable[Quarter]<=MAX(Measuretable[Quarter])))
    );
    CALCULATE(
        SUM(Measuretable[Budget]);
        ALL(Measuretable[Quarter])
     )
 )

In the chart below the endresult should be instead:

• dark column Q3: 22,2%
• dark column Q4: 22,2% + 30,5%: 52,7%

The business question is actually something different. I need this technique to calculate a forecast. It is however pragmatic but maybe interesting to share

• Forecast for Q3 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q3
• Forecast for Q4 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q4

What this will actual say is when we use as much budget in Q3 and Q4 as previous year, we will go over budget (more than 100% in Q4) or we will still have enough budget.

I hope this info can help to gain insight in the problem. I am happy to answer more questions if necessary!

Friendly greets