topic Re: Cash Collected Per Day Based Off Working Bank Days in DAX Commands and Tips

Cash Collected Per Day Based Off Working Bank Days

CracktheCode85 — Wed, 24 Apr 2024 13:32:36 GMT

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.

Please help me find a solution to this troubling issue.

Re: Cash Collected Per Day Based Off Working Bank Days

lbendlin — Wed, 24 Apr 2024 23:50:06 GMT

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

Re: Cash Collected Per Day Based Off Working Bank Days

CracktheCode85 — Thu, 25 Apr 2024 13:07:31 GMT

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

Re: Cash Collected Per Day Based Off Working Bank Days

lbendlin — Thu, 25 Apr 2024 13:16:29 GMT

Why is your rolling average for Day 1

$47,777,328.62

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

Re: Cash Collected Per Day Based Off Working Bank Days

CracktheCode85 — Thu, 25 Apr 2024 13:19:42 GMT

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 .

Re: Cash Collected Per Day Based Off Working Bank Days

lbendlin — Thu, 25 Apr 2024 13:30:57 GMT

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