Cumulative Percentage Calculations by Month Total

[Xavianna]

Hello,

I am trying to calculate the cumulative percentage of booking received for each month but filled in the consecutive months.

The example below is for all bookings received in April but filled in the following months. I would like it to calculate like this:

Apr - 24/59 = 40.7%

May - 31/59 = 52.5%

Jun - 37/59 = 62.7%

Jul - 39/59 = 66.1%

This is to identify how many months it takes to reach 100% fill for the bookings in each month.

The current formula I have does the cumulative against the total 24/124 etc. What am I missing to filter it by month?

Total Filled YTD Running % = Table[Total Filled YTD]/CALCULATE(Table[.Bookings Requested New], ALL(Table),DateTable2[Date])

Thank you for your help in advance.

[AntrikshSharma]

@Xavianna Provide the data after removing confidential information.

[Xavianna]

I'm unable to attach Excel spreadsheets here and the table has about 200 rows, it will be too large to paste as a table.

The main columns I use for this table are:

- Date vacancy received (April - Aug)

- Date vacancy filled (April - Aug)

- I have a Dax to count rows for all vacancy received and filled

Does this help?

[AntrikshSharma]

@Xavianna You can upload on Google drive and share the link.

[Xavianna]

https://docs.google.com/spreadsheets/d/1_QaXBTc-n84Rd_otNQLsQHV_CTrh-IcP/edit?usp=drive_link&ouid=11...

I included the status column which says whether the booking/vacancy is filled, cancelled etc.

The monthly total sum I would like to use excludes the "cancelled" status. So any bookings received that hasn't been cancelled basically.

Thanks for your help in advance.

[AntrikshSharma]

@Xavianna Remove the request for permission option and share again.

[Xavianna]

Hopefully its ok now. Please try again.

[AntrikshSharma]

@Xavianna Is this what you want? Also, I didn't understand what you meant by "The current formula I have does the cumulative against the total 24/124 etc. What am I missing to filter it by month?"

[Xavianna]

Yes!! This is exactly what I need, thank you so much. Could you please share the dax formula and also share how you manage to put the whole month's total  bookings received in the 3rd column?

This is what I meant before, my current formula does 24/124, 31/124, 37/124 etc instead of 24/59, 31/59, 37/59 like yours is doing. I have attached the example so hope it makes sense.

Thanks so much 🙂

[AntrikshSharma · Accepted solution]

@Xavianna You can download the PBIX from here: https://drive.google.com/file/d/1reAU34d5T6x8aD5_l3fY_b0kOSnOOb_a/view?usp=sharing

[Xavianna]

Thank you so much 🙂

Any idea why this formula doesn't work for me please?

Bookings Received =

CALCULATE (

    [Bookings],

    Bookings[Status] <> "Cancelled",

    REMOVEFILTERS ( Bookings[Bookings Filled (Month/Year)] )

)

For some reason, Table[Bookings Filled (Month/Year)] doesn't appear on the list when I was amending the Dax. Table[Bookings Filled (Month/Year)] isn't causing an error and the measure is created.

Thanks 🙂

[AntrikshSharma]

Your column names might be different.

[Xavianna]

I have adjusted them to the column names mirroring your PBIX.

Bookings Received =

CALCULATE (

    [Bookings],

    Bookings[Status] <> "Cancelled",

    REMOVEFILTERS ( Bookings[Bookings Filled (Month/Year)] )

)

So for Bookings Received:

[Bookings] - Countrows Bookings (which I already have)

Bookings[Status] - which was part of the dataset

Bookings[Bookings Filled (Month/Year)] - Is this Dax

Bookings Filled (Month/Year) =

-- FORMAT ( Bookings[Date Filled], "MM/YYYY" )

STARTOFMONTH ( Bookings[Date Filled] )

I have all of the measures and the only new measure I had to create was MyTableName[Bookings Filled (Month/Year)]. However, my Dax is giving this error "Column 'Bookings Filled (Month/Year)' in table 'MyTableName' cannot be found or may not be used in this expression."

Am I missing a step somewhere?

[Xavianna]

Did you create this as a "New column"?

When I created it as a measure, it didn't come up with any error but it is not showing for me to call on to the Bookings Received measure. However, when I created it as "New column",  which is what it looks like in the PBIX when I looked closely, this is the error I get.

I'm at a loss... The table data is the exact same as what I provided, I only removed the other unnecessary columns.

[Xavianna]

I got it working now. New column and repasting the formula works. Thank you so so much :)!!

[Xavianna]

Does anyone have any idea why April 2023 is not showing in the bar chart when I'm using these measures Bookings Filled (Month/Year) & Bookings Requested (Month/Year)?

It is in the data column...

Thanks in advance 🙂