cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Cash Collected Per Day Based Off Working Bank Days

Hi Super Users!

I'm struggling with a measure that should easily calculate the average cash collected per each Bank Day and then provide a total of that 'running average' on a card up to the current date.

I believe I have a huge issue with my date table and data table not communicating properly, however all other widgets and data points are accurate so I'm a  bit stumped.

As you can see from the screen shot my dates do line up with the Bank Days. Duplicate Bank Days indicate a Saturday and Sunday, that roll up into the upcoming monday.

However out of the two measures in this screen shot only one is correct, and it only works for the exact current day we are up to.
We can't look back at previous months to see the month end avg for Jan, Feb, March....
This is my measure for 'Cash Per Day Test' which is displaying the accurate figure:

Cash Per Day Test = DIVIDE(SUM('Table'[payments]),
CALCULATE(DISTINCTCOUNT('Date'[Bank Day Number]),
FILTER('Date'
,'Date'[Bank Day Number] >= 1
&& 'Date'[Bank Day Number] <=CALCULATE(MAX('Date'[Bank Day Number]), FILTER('Date','Date'[Date] = IF('Date'[Date].[Date], Today()-4, 'Date'[Date].[Date]))
))))

I wanted to write a conditional statement to tell the visual if the month changes on the slicer then we want to see that amount for that date. But it won't work, I just get a blank.

5 REPLIES 5
Super User

Do you want a cumulative average or a rolling average?  If rolling, what is your window size?

Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Helper I

These Averages change each day as new data refreshes, but I also need to see what the previous month was at the month end.

For example on Day 5 (which rolls up to 4/10 due to weekend and holiday) we see the average

\$36M

The next day as the data refreshes we should se Day 6 = \$34.5M and so forth.

While using a slicer to view historical month end numbers for example:
If we want to view March then we should see the that Bank Day Average of Payments for that day.

 Date Payment Date Bank Day Expected Result Payment Per Day 4/1/2023 \$10,406.73 Day 1 4/1/2023 Day 1 (Rolling Average Based on Bank Days) 4/2/2023 \$9,337.18 Day 1 4/2/2023 Day 1 \$47,777,328.62 Day 1 4/3/2023 \$47,777,328.62 Day 1 4/3/2023 Day 1 \$38,612,580.12 Day 2 4/4/2023 \$29,447,831.62 Day 2 4/4/2023 Day 2 \$34,701,092.14 Day 3 4/5/2023 \$26,878,116.18 Day 3 4/5/2023 Day 3 \$33,281,595.45 Day 4 4/6/2023 \$29,023,105.36 Day 4 4/6/2023 Day 4 \$36,047,251.83 Day 5 4/7/2023 \$20,032,374.11 Day 5 4/7/2023 Day 5 \$34,518,587.57 Day 6 4/8/2023 \$24,848.58 Day 5 4/8/2023 Day 5 \$33,074,613.59 Day 7 4/9/2023 \$8,969.96 Day 5 4/9/2023 Day 5 \$33,267,464.10 Day 8 4/10/2023 \$47,109,877.38 Day 5 4/10/2023 Day 5 \$33,015,576.54 Day 9 4/11/2023 \$26,875,266.28 Day 6 4/11/2023 Day 6 \$34,168,759.49 Day 10 4/12/2023 \$24,410,769.71 Day 7 4/12/2023 Day 7 4/13/2023 \$34,617,417.64 Day 8 4/13/2023 Day 8 4/14/2023 \$31,000,476.07 Day 9 4/14/2023 Day 9 4/15/2023 \$133,899.56 Day 10 4/15/2023 Day 10 4/16/2023 \$15,463.95 Day 10 4/16/2023 Day 10 4/17/2023 \$44,547,406.04 Day 10 4/17/2023 Day 10
Super User

Why is your rolling average for Day 1

 \$47,777,328.62

?  What happened to the Payments for 4/1 and 4/2 ?

Helper I

Day 1 should be a sum of April 1st - April 3rd.

 4/3/2023 \$47,797,072.53 Day 1

is the accurate figure. Sorry about that .