Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply

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. 
 
Please help me find a solution to this troubling issue. 

CracktheCode85_0-1713965428923.png

 

5 REPLIES 5
lbendlin
Super User
Super User

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

 

lbendlin_0-1714051851726.png

 

lbendlin
Super User
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.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

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.

 

 

 

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

Why is your rolling average for Day 1 

$47,777,328.62

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

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

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

  is the accurate figure. Sorry about that . 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.