Calculate MTD of current month only without date slicer dependency

[rahul-opp]

Hello
I want to create a line chart comparison between Current Month MTD and Current Month Projected revenue. On report page, there is Date range slicer.
I have created basic Line chart which shows MTD of selected date range.
MTD revenue measure formula used :

MTD Revenue =  TOTALMTD([Total Sales], 'Calendar'[Date]) 

where [Total Sales] is measure

Total Sales =
CALCULATE(
 SUM('Sales'[Total Price])
) 

I also tried following formula to restrict date range, but this also didn't worked

CALCULATE(
   [Total Sales],
   DATESMTD('Calendar'[Date]),
   MONTH('Calendar'[Date]) = MONTH(TODAY()),
   YEAR('Calendar'[Date]) = YEAR(TODAY()),
   REMOVEFILTERS('Calendar'[Date])
)

As per this line chart gets generated correctly.
BUT my requirement is, what ever date range selected, Line chart should show CURRENT Month data only. Don't want date slicer selection for Line chart.
I tried to remove Date slicer interactions from Format => Edit interactions , but it breaks line chart.
This is happening because both line chart and date slicer use same 'Calendar'[Date].

Following image of Line chart with date range selected. I want same line chart but without any dependecy of Date Slicer.
Any ideas how to plot line chart without date slicer dependency?


If I remove date slicer interactions from line chart , it appears as below, which is wrong

[v-jianpeng-msft · Accepted solution]

Thank you johnt75 

Hi, @rahul-opp 

Based on your description and the screenshots you showed, I created the following sample data:

I also have a date table:

First, turn off the effect of the slicer on the line chart:

Next, create a new calculated column in the date table:

Is current Month = 
IF(MONTH('Calendar'[Date]) = MONTH(TODAY()) && YEAR([Date]) = YEAR(TODAY()),1,0)

Finally, use this calculated column in the line chart:

When we have data updates, this chart will also dynamically display the MTD for the latest month.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rahul-opp]

hi@v-jianpeng-msft and @johnt75 Thanks for solution. It's new learning for me. I have used both of your suggestions.
@v-jianpeng-msftI have used your solution but it didn't worked. Reason : In your file in Calendar or Date table you have max date is till 7-Nov-2024 and in my calendar i have dates till year end. So it was not working on my end. So I used condition suggested by @johnt75 , I have added 'Calendar'[Date] <= TODAY() in column formula.
Final result is :::


This question is only half part of what I want to achieve.
I want to draw line chart comparison between Current Month MTD and Current Month Total Projected revenue.
I need similar result from following link
https://community.fabric.microsoft.com/t5/Desktop/power-bi-Monthly-forecast/td-p/2497222 

Still working on Total projected revenue formula which is combination of MTD and projected revenue. May be I will raise another question in forum for this if I get stuck.
@johnt75 @v-jianpeng-msft If I add MTD and Total projected revenue to my line chart, Does column formula suggested by you will work? Does it give similar output as above image ?
Please share your thoughts.

[v-jianpeng-msft · Accepted solution]

Thank you johnt75 

Hi, @rahul-opp 

Based on your description and the screenshots you showed, I created the following sample data:

I also have a date table:

First, turn off the effect of the slicer on the line chart:

Next, create a new calculated column in the date table:

Is current Month = 
IF(MONTH('Calendar'[Date]) = MONTH(TODAY()) && YEAR([Date]) = YEAR(TODAY()),1,0)

Finally, use this calculated column in the line chart:

When we have data updates, this chart will also dynamically display the MTD for the latest month.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Try turning off the interactions between the slicer and the line chart, then change the filter on the line chart for the Date column to be relative date, is in the current month.

[rahul-opp]

hi @johnt75 thanks for reply. Your solution restricted date range to current month without date range. It appears as below

Is this correct ? I mean it should show line till current day 6-Nov-2024. or it should show all dates of current month as shown in above image.
Currently it shows all dates of current month with last MTD value from 7-Nov onwards.

[rahul-opp]

Any suggestion, any other way,  how to show line chart till current date for example in above image current date is 6-Nov-2024?

[johnt75 · Accepted solution]

I can think of a couple of options. You could add a column to your date table like

Today or Before = IF( 'Date'[Date] <= TODAY(), 1 )

and use that as a filter on the visual, in addition to the current month filter.

Or you could amend the measure to return BLANK() if the date is after today.