topic Re: Cumulative Percentage Calculations by Month Total in DAX Commands and Tips

Cumulative Percentage Calculations by Month Total

Xavianna — Tue, 03 Oct 2023 14:20:56 GMT

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.

Re: Cumulative Percentage Calculations by Month Total

AntrikshSharma — Tue, 03 Oct 2023 14:54:32 GMT

Xavianna Provide the data after removing confidential information.

Re: Cumulative Percentage Calculations by Month Total

Xavianna — Tue, 03 Oct 2023 15:04:03 GMT

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?

Re: Cumulative Percentage Calculations by Month Total

AntrikshSharma — Tue, 03 Oct 2023 15:06:45 GMT

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

Re: Cumulative Percentage Calculations by Month Total

Xavianna — Tue, 03 Oct 2023 15:29:12 GMT

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

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.

Re: Cumulative Percentage Calculations by Month Total

AntrikshSharma — Tue, 03 Oct 2023 15:33:25 GMT

Xavianna Remove the request for permission option and share again.

Re: Cumulative Percentage Calculations by Month Total

Xavianna — Tue, 03 Oct 2023 15:51:29 GMT

Hopefully its ok now. Please try again.

Re: Cumulative Percentage Calculations by Month Total

AntrikshSharma — Wed, 04 Oct 2023 09:16:41 GMT

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?"

Re: Cumulative Percentage Calculations by Month Total

Xavianna — Wed, 04 Oct 2023 13:22:38 GMT

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 🙂

Re: Cumulative Percentage Calculations by Month Total

AntrikshSharma — Wed, 04 Oct 2023 13:37:30 GMT

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

Re: Cumulative Percentage Calculations by Month Total

Xavianna — Wed, 04 Oct 2023 14:38:13 GMT

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 🙂

Re: Cumulative Percentage Calculations by Month Total

AntrikshSharma — Wed, 04 Oct 2023 15:07:07 GMT

Your column names might be different.

Re: Cumulative Percentage Calculations by Month Total

Xavianna — Wed, 04 Oct 2023 15:12:50 GMT

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?

Re: Cumulative Percentage Calculations by Month Total

Xavianna — Wed, 04 Oct 2023 15:29:20 GMT

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.

Re: Cumulative Percentage Calculations by Month Total

Xavianna — Wed, 04 Oct 2023 15:56:21 GMT

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

Re: Cumulative Percentage Calculations by Month Total

Xavianna — Wed, 04 Oct 2023 16:00:44 GMT

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 🙂