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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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