cancel
Showing results for
Did you mean:

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

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.

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

1 ACCEPTED SOLUTION
Helper I
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] )
3 REPLIES 3
Super User

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

Helper I
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] )
Helper I

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:

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors