Forum Discussion
Helper IICash 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:
5 Replies
- lbendlin
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-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
CracktheCode85These 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 - lbendlin
Why is your rolling average for Day 1
$47,777,328.62
? What happened to the Payments for 4/1 and 4/2 ?
- lbendlin
Do you want a cumulative average or a rolling average? If rolling, what is your window size?
