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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
RD_PowerBI24
Helper I
Helper I

MTD Flatline after latest daily sales. How to hide/remove?

Hi,

 

I currently have a visual for MTD sales, where after the latest daily sales, the rest of the day for the month flatlines, with no new sales. How can I stop this so that the MTD sales stops as the latest day.

 

RD_PowerBI24_0-1713823840599.png

 

I have used the following with no success:

 

MTD_Sales = if(ISBLANK(TOTALMTD(SUM(All_Sales[Total_Sales]), All_Sales[All_Invoice_Date].[Date])),BLANK(),CALCULATE(

    TOTALMTD(SUM(All_Sales[Total_Sales]),All_Sales[All_Invoice_Date].[Date],FILTER(

        ALLSELECTED(All_Sales[Invoice Date].[Date]),

        ISONORAFTER(All_Sales[Invoice Date].[Date], MAX(All_Sales[Invoice Date].[Date]), DESC)

    ))))

MTD_Sales = TOTALMTD(SUM(All_Sales[Total_Sales]),All_Sales[All_Invoice_Date].[Date])

 

N.B.  All_Sales[All_Invoice_Date] is aligned to the dates for the budgets  and sales

 

RD_PowerBI24_2-1713824049132.png

 

1 ACCEPTED SOLUTION

I solved this by using a calculated column on the sales fact dates, which gave a true/false value. Then filtered the table on this column to hide the false. DatesWithSales = 'Date'[Date] <= MAX ( Sales[Order Date] )

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

Instead of ISONORAFTER(DESC) use DATESBETWEEN the MIN and MAX fact dates for each period.

I solved this by using a calculated column on the sales fact dates, which gave a true/false value. Then filtered the table on this column to hide the false. DatesWithSales = 'Date'[Date] <= MAX ( Sales[Order Date] )

Hi, I tried this by amending it to:

 

MTD_Sales = if(ISBLANK(TOTALMTD(SUM(All_Sales[Total_Sales]), All_Sales[All_Invoice_Date].[Date])),BLANK(),CALCULATE(

    TOTALMTD(SUM(All_Sales[Total_Sales]),All_Sales[All_Invoice_Date].[Date],FILTER(

        ALLSELECTED(All_Sales[Invoice Date].[Date]),

        DATESBETWEEN(All_Sales[Invoice Date].[Date],MIN(All_Sales[Invoice Date].[Date]),MAX(All_Sales[Invoice Date].[Date])

    )))))

 

But the visual breaks and I get this error:

RD_PowerBI24_0-1713853083504.png

FYI: All_Invoice_Date is for all dates taken from Budget or Sales tables, whereas Invoice Date is for sales, which is what I want to restrict the sum on here.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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