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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RD_PowerBI24
Helper I
Helper I

Carrying forward the MTD sales to next working day when 0 sales for the Working Day

Hi,

 

I have a measure that calculates the MTD sales using:

 

MTD Batch Sales = if(ISBLANK(SUM('Sales Report incl  Batch'[Qty Shipped])),BLANK(),CALCULATE(
    SUM('Sales Report incl  Batch'[Qty Shipped]),
    FILTER(
        ALLSELECTED('Working Day'[Cumulative Days]),
        ISONORAFTER('Working Day'[Cumulative Days], MAX('Working Day'[Cumulative Days]), DESC)
    )
))
 
This works in theory to output the total sales up to the latest working day. However, on certain days, when there are no sales, the output is a blank for that working day. Example for Working Day 2 and 5 below, there were no sales on that day and a blank appeared. 
 
I would like to ouput max sales up to that day, so that Day 2 would be 28 and and Day 5 would be 57, taking the previous days' max sales. I assume it's replacing the "Blank()" in the IF statement's true condition, but have tweaked around with a -1 day but either my syntax and/or methodology was wrong. Please can someone help how to fix this. Thanks.
 
RD_PowerBI24_0-1712608081878.png

 

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

@Ashish_Mathur  Thanks for your contribution on this thread.

Hi @RD_PowerBI24 ,

Base on your description, it seems that you want to update with the max sales in previous culmulative days if the sales in that day is blank. Am I right? Could you please provide some sample data in the table 'Working Day' and 'Sales Report incl  Batch' with Text format and your expected result with backend logic and special examples? By the way, is there any relationship between these two tables? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

 

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

I do not understand the Cumulative Days field.  That should be a proper date column/field from the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.