Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Do you want a cumulative average or a rolling average? If rolling, what is your window size?
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.
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 |
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.53 | Day 1 |
is the accurate figure. Sorry about that .
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |