Forum Discussion

niculeica's avatar
niculeica
Helper I
2 years ago
Solved

Calculate Measure1 as Measure2 + Measure3 Running Total

Hi,   I'm trying to calculate Measure1 (Total Value) as Measure2 (Signed Contracts) + all backlog of Measure3 (Pending Contracts), everything displayed by date hierarchy (Year, Quarter, Month). So ...
Need Help
  • speedramps's avatar
    speedramps
    2 years ago

    Click here to download the solution

    Download PBIX 

     

    How it works ...
    Create a Calandar table

     

    Create a measure

    YTD cumulative = 
VAR myyear = SELECTEDVALUE('Calendar'[Year])
VAR myperiod = MAX('Calendar'[Date])
RETURN
CALCULATE(
    SUM(Facts[Amount in company currency]),
    ALL('Calendar'),
    'Calendar'[Year] = myyear &&
    'Calendar'[Date] <= myperiod 
)

     

    Dsiplay in matrix

     

    Thanks for the clear description of the problem with example data. I wish everyone did that!

    Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the thumbs up and the [accept as solution] button.  Thnak you.

niculeica's avatar
niculeica
Helper I
2 years ago

speedramps, at the very bottom is a data sample.

Needed:

- one measure = sum of Amount per year, quarter and month (date hierarchy) - in matrix;

- one measure = cummulative sum of Amount per year, quarter and month;

- desired output (cummulative sum of last month of q is equal to cummulative sum of q) right below. I hope it makes sense.

 

 SumCummulative Sum
Jan1010
Feb3040
Mar60100
Total Q1100100
Apr30130
May25155
Jun20175
Total Q275175

 

 

 

Record IDDeal NamePipelineDeal StageAmount in company currencyClose DateInactive Date
15860859474Deal no. 142Renewals - ARR>365 Days19942024-12-01 02:002024-12-01
15842177351Deal no. 124Renewals - ARR<365 Days15182024-11-01 02:002024-11-01
15678677277Deal no. 40Renewals - ARR<365 Days9992024-10-01 03:002024-10-01
15611462181Deal no. 269Renewals - ARR>365 Days9952026-09-05 03:002026-09-05
15557243932Deal no. 282Renewals - ARR<365 Days9282024-11-01 02:002024-11-01
15541818683Deal no. 83Renewals - ARR<365 Days5242024-11-01 02:002024-11-01
15516131724Deal no. 195Renewals - ARR>365 Days15292026-10-01 03:002026-10-01
15425010614Deal no. 246Renewals - ARR<365 Days18302024-11-01 02:002024-11-01
15421474859Deal no. 154Renewals - ARR>365 Days18242025-01-01 02:002025-01-01
15412867601Deal no. 77Renewals - ARR<180 Days12142024-03-10 02:002024-03-10
15279595559Deal no. 193Renewals - ARR<90 Days10272024-01-01 02:002024-01-01
15164440081Deal no. 58Renewals - ARR<270 Days13342024-06-01 03:002024-06-01
15164232203Deal no. 119Renewals - ARR<90 Days8462024-01-01 02:002024-01-01
15118337967Deal no. 51Renewals - ARR>365 Days7162026-03-01 02:002026-03-01
14994331225Deal no. 118Renewals - ARR<180 Days6542024-03-01 02:002024-03-01
14992063493Deal no. 67Renewals - ARR<90 Days14182024-01-01 02:002024-01-01
14982132624Deal no. 173Renewals - ARR<270 Days5062024-07-01 03:002024-07-01
14981603117Deal no. 208Renewals - ARR<365 Days10782024-09-01 03:002024-09-01
14980389804Deal no. 288Renewals - ARR<30 Days13502023-10-01 03:002023-10-01
14932638466Deal no. 270Renewals - ARR<30 Days5162023-09-01 03:002023-09-01
14873726787Deal no. 263Renewals - ARR<180 Days16932024-03-01 02:002024-03-01
14824777142Deal no. 106Renewals - ARR<180 Days6152024-04-01 03:002024-04-01
speedramps's avatar
speedramps
Super User
2 years ago

Click here to download the solution

Download PBIX 

 

How it works ...
Create a Calandar table

 

Create a measure

YTD cumulative = 
VAR myyear = SELECTEDVALUE('Calendar'[Year])
VAR myperiod = MAX('Calendar'[Date])
RETURN
CALCULATE(
    SUM(Facts[Amount in company currency]),
    ALL('Calendar'),
    'Calendar'[Year] = myyear &&
    'Calendar'[Date] <= myperiod 
)

 

Dsiplay in matrix

 

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the thumbs up and the [accept as solution] button.  Thnak you.

  • niculeica's avatar
    niculeica
    Helper I
    2 years ago
    Thank you, speedramps! I was able to put the solution to use.